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
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