Use a built-in function that will calculate the total number of Comedy, Horror, Action and Drama films in cells N2 to N5.



Section is the quiz available on Moodle site “IEPV201 TEST 1 QUIZ” SECTION B

Download the Test 1 Section Two – Spreadsheet and answer the following questions.


1.       Use a built-in function that will calculate the total number of Comedy, Horror, Action and Drama films in cells N2 to N5.

2.       Use a function N8 to find any film by an Actor Taylor Robert.                                                       [6]

3.       Use a function N9 to find any film by an Actress Barash Olivia.                                                     [6]

4.       In cell O9, use a function to return the year of the film you have found in the question above. [6]

5.       In Column J, use of a formula to find a drama film that has won an award and with a popularity of more than 80. The formula must display the title of the film if it finds it otherwise it must display nothing if the film is not found.                                                                                                                                              [10]

6.       In cell N12, use a function to check if there is a film called “A New Life” return the row

number where is found.                                                                                                                               [4]

7.       In Column K, use a function(s) to return a film from the year 1988 OR a War film OR a film with a popularity rate of more than 50.                                                                                                                                     [10]

8.       In cell N13, use a VLOOKUP function to find the director of the film called “Magic” in cell M13, make use of an exact match.                                                                                                                                           [6]

9.       Use an IF function in column L to return the film Title and the Actor IF the film has a Popularity rate of less than 10. Concatenate your output to the following output (Cuba – Connery, Sean). [7]

10.   Based on the previous question, in Column L use a conditional formatting to highlight all

cells that contain “Connery Sean” with a light red fill and dark red text.                                   [6]

11.   Save the spreadsheet as “Your name and surname – Student number”                                  [4]


Section Total [65]












X Zepe

Related Questions in engineering category