Apply Conditional Formatting To Highlight Assessment Answer

Tasks

Work on each instruction in the correct worksheet.

Sheet 1  

T1:  Add your name and ID in cells A1 and B1 respectively.

T2:  Rename the worksheet name Sheet1 to TV Advertisement.

T3:  In the cells G8 to G15, use a formula to calculate the difference of cost between 2018 and 2019 (positive result if the cost in 2019 is higher). Read notes above.

T4: In H8 to H15, use a formula to calculate the percentage of difference between 2018 and 2019. 

T5: In I8 to I15, use a formula to calculate the projected cost in 2020 based on the cost in 2019 and the percentage of difference with the predicted growth.

Hint: Use the result of column H added with the rate of yearly growth (C17), then use it with the cost in 2019. Read notes above.

Example: If the difference is 1.5% and the growth rate is 1.12%, then 2020 cost should be an increase of 2.62% from 2019 cost.

T6:  In B20 to B22, use a predefined function to calculate the average cost of advertisement in 2019 according to the age restriction classifications (PG, M, MA15+). Read notes above.

T7:  In F20 to I21, use a predefined function to calculate the minimum and maximum advertisement cost in 2019 for each network (ABC, CBS, Fox, NBC). Read notes above.

T8:  Use your creativity to apply general formatting to improve the readability of data within this worksheet. No changes should be made to the original nature of the worksheet (rows, columns, placement of data, etc.)

T9: Add a bar chart for the average cost based on age restriction classifications. Include the necessary chart labels.

T10:   Add a line chart with markers to compare the minimum and maximum cost in 2019 (2 series). Include the necessary chart labels.heet2 

T11:  Apply conditional formatting to highlight the top 15 ratings in green.

T12:  Apply conditional formatting to highlight the classification for mature audiences (M) in red.

Sheet 3  

T13:  Sort the data based on the title in descending order.

Sheet 4

T14:  Sort the data based on the Network (ascending), then by Classification (descending), then by Ratings (descending).

Sheet 5

T15:  Use filter to show only TV series from Netflix with ratings above 8.5 (exclusive).

Sheet 6

T16:  Use the Subtotal button in Data tab to organise data to include the average of ratings for each network.

Note: depending on your MS Excel version, an additional step may be required before applying subtotal.

Sheet 7

T17: Use the data to create a new worksheet called Pivot Table containing a pivot table that shows the maximum ratings for each network with different classifications in the columns. Add start year as the filter.

T18:  Filter the data of the pivot table created to show only information for ABC, CW and Fox that started in 2015 to 2018.