Microsoft Excel Test

Question 1
Consider the following Excel screenshot:

Cells C10:C13 are a named range called “Province”.
Which of the following is the correct entry in the ‘Source’ field in the data validation dialogue box to display the drop-down list in the cell F4 if cells C10:C13 is a named range called “Province”?
A)
B)
C)
D)

Ontario,Quebec,Alberta,British Columbia
=C10:C13
Province
={Ontario,Quebec,Alberta,British Columbia}

Questions 2, 3
Consider the following Excel screenshot:

Which of the following is the correct formula to look up 2016 Taxes in cell O7?
A)
B)
C)
D)

=VLOOKUP(N7,B4:G12,7, FALSE)
=HLOOKUP(O6,B5:K12,7,FALSE)
=INDEX(G4:K11,MATCH(O6,G4:H11,0),MATCH(N7,G4:K11,0))
=INDEX(B4:K11,MATCH(N7,B4:B11,0),MATCH(O6,B4:K4,0))

If cell G4’s data entry is 2015, what number format is being used in G4?
A)
B)
C)
D)

General\E
0.00 + “E”
#,##0 + “E”
_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

Questions 4, 5
What is the INDIRECT equivalent of the formula ‘=COUNTIF(‘Task1’!A3:A25,”>0”) if the string
‘Task1’ is contained in cell A1?
A)
B)
C)
D)

=INDIRECT(“COUNTIF(‘”&A1&”’!A3:A25,”>0”))
=COUNTIF(INDIRECT(“’”&A1&”’!A3:A25,”>0”))
=COUNTIF(“’”&INDIRECT(A1)&”’!A3:A25,”>0”))
=COUNTIF(“’”&A1&”’!”&INDIRECT(“A3:A25,”>0”))

Which of the following is the correct formula to skip a weekend day in a column of consecutive dates
(assuming it is written in cell A7)?
A)
B)
C)
D)

=IF(OR(WEEKDAY(A6+1)=1,WEEKDAY(A6+1)=7),A6+3,A6+1)
=IF(WEEKDAY(A6+1)=1,A6+2,IF(WEEKDAY(A6+1)=7),A6+3,A6+1)
=IF(WEEKDAY(A6+1)=7,A6+3,A6+1)
=IF(AND(WEEKDAY(A6+1)=1,WEEKDAY(A6+1)=7),A6+3,A6+1)

Answers
1.
2.
3.
4.
5.

B
D
A
B
C