Use a built-in function that will calculate the total number of Comedy, Horror, Action and Drama films in cells N2 to N5.
engineering
Description
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.
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
