EXCEL CRASH EXAMS WITH VERIFIED QUESTIONS
AND CORRECT ANSWERS
All of the following are keyboard shortcut that allow the user input to add more sheets to a
workbook EXCEPT:
Alt i w
Alt h i s
Shift F11
Alt Shift F1
Alt h i w
Incorrect
- ANSWERS-Alt i w, Alt h i s, Shift F11 and Alt Shift F1 are all keyboard shortcuts for adding
more sheets to a workbook.
Alt h i w is not a valid shortcut.
Below is a Data Table designed to display the effect on 2015 Net Profit based of changes in
2015 Revenue Growth and 2015 COGS %. Please provide:
The cell reference required in D22.
The row input cell required by the Data Table.
The column input cell required by the Data Table.
Provide all three cell references, in order above, separated by commas with no space (i.e.
A1,A2,A3) Note: Do not include spaces within answer
- ANSWERS-The correct answer is: E8,E10,E11
E8 is the cell reference for 2018 Net Income, the output variable, which must be in the top left
corner of the table. E10, the 2015 revenue growth rate, is the row input cell, the variable that is
being sensitized in the top row of the table. E11, the COGS %, is the column input variable that is
being sensitized.
Identify a function in cell D6 that will return the fraction of the year elapsed assuming a 360 day
count basis.
=STUB(D4,D5)
=YEARFRAC(D4,D5,2)
=DAYS360(D4,D5)
=YEARFRAC(D4,D5)
- ANSWERS-The correct answer is: =YEARFRAC(D4,D5,2). STUB is not an Excel formula. DAYS360
will return the number of days in a 360 day year. YEARFRAC(D4,D5) will calculate the fraction
using a 365-day year.
Identify the best formula that will output 2016 expenses. Hint: Only select the 'range lookup'
argument if it is necessary.
=HLOOKUP(2016,B2:D4,2,0)
=HLOOKUP(2016,A1:D4,3)
=HLOOKUP("Expenses",A1:D4,2)
, =HLOOKUP(2016,B2:D4,2)
- ANSWERS-The correct answer is: =HLOOKUP(2016,A1:D4,3)
=HLOOKUP(2016,B2:D4,2,0) will not result in a valid output because 2016 is not selected in the
table array argument.
=HLOOKUP("Expenses",A1:D4,2) will result in a #NAME error because "Expenses" is not a
horizontal lookup reference.
=HLOOKUP(2016,B2:D4,2) will reference the Revenue line rather than the expenses line because
the third argument references the row 2 in the table array.
Identify the commands to insert a comment and delete a comment
1)Alt e m 2) Alt e m
1)Alt h e m 2) Alt I m
1)Shift F2 2) Alt h e m
1)Alt h e m 2) Alt I s
- ANSWERS-The correct answer is Shift F2, Alt h e m.
Alt e m is a shortcut to open the menu to move or copy a selected sheet. Alt I s is the shortcut to
insert a symbol.
Identify the formula that outputs 2016 expenses.
=VLOOKUP("Expenses",A1:D4,3,1)
=VLOOKUP("Expenses",A1:D4,3)
=VLOOKUP(2016,A1:D4,3)
=VLOOKUP("Expenses",A1:D4,3,0)
- ANSWERS-The correct answer is: =VLOOKUP("Expenses",A1:D4,3,0)
=VLOOKUP("Expenses",A1:D4,3,1) looks for an approximate match and requires that the
reference range be in ascending order.
=VLOOKUP("Expenses",A1:D4,3) also looks for an approximate match as specified by match
type 1, and requires that the reference range be in ascending order.
Identify the formula that outputs data from the table based on the user inputs in cells C6 and C7.
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1))
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1))
=OFFSET(A1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1))
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0))
- ANSWERS-The correct answer is: =OFFSET(A1, MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)).
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1)) does not return the correct value because an
exact match is not specified for the MATCH function used for the 'row' argument of the OFFSET
function.
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)) uses an incorrect cell reference for the
reference argument of the OFFSET function, which must be in the upper left corner of the table.
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0)) reverses the MATCH functions used for the
row and column references in the OFFSET function, returning #N/A.
Identify the formula that outputs the sum of property values for properties in Boston that were
built after 1950.
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
=SUMIFS(C2:C5,"Boston",D2:D5,">1950",B2:B5)
=SUMIF(B2:B5,and(D2:D5,">1950",C2:C5,"Boston")
- ANSWERS-The correct answer is: =SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
AND CORRECT ANSWERS
All of the following are keyboard shortcut that allow the user input to add more sheets to a
workbook EXCEPT:
Alt i w
Alt h i s
Shift F11
Alt Shift F1
Alt h i w
Incorrect
- ANSWERS-Alt i w, Alt h i s, Shift F11 and Alt Shift F1 are all keyboard shortcuts for adding
more sheets to a workbook.
Alt h i w is not a valid shortcut.
Below is a Data Table designed to display the effect on 2015 Net Profit based of changes in
2015 Revenue Growth and 2015 COGS %. Please provide:
The cell reference required in D22.
The row input cell required by the Data Table.
The column input cell required by the Data Table.
Provide all three cell references, in order above, separated by commas with no space (i.e.
A1,A2,A3) Note: Do not include spaces within answer
- ANSWERS-The correct answer is: E8,E10,E11
E8 is the cell reference for 2018 Net Income, the output variable, which must be in the top left
corner of the table. E10, the 2015 revenue growth rate, is the row input cell, the variable that is
being sensitized in the top row of the table. E11, the COGS %, is the column input variable that is
being sensitized.
Identify a function in cell D6 that will return the fraction of the year elapsed assuming a 360 day
count basis.
=STUB(D4,D5)
=YEARFRAC(D4,D5,2)
=DAYS360(D4,D5)
=YEARFRAC(D4,D5)
- ANSWERS-The correct answer is: =YEARFRAC(D4,D5,2). STUB is not an Excel formula. DAYS360
will return the number of days in a 360 day year. YEARFRAC(D4,D5) will calculate the fraction
using a 365-day year.
Identify the best formula that will output 2016 expenses. Hint: Only select the 'range lookup'
argument if it is necessary.
=HLOOKUP(2016,B2:D4,2,0)
=HLOOKUP(2016,A1:D4,3)
=HLOOKUP("Expenses",A1:D4,2)
, =HLOOKUP(2016,B2:D4,2)
- ANSWERS-The correct answer is: =HLOOKUP(2016,A1:D4,3)
=HLOOKUP(2016,B2:D4,2,0) will not result in a valid output because 2016 is not selected in the
table array argument.
=HLOOKUP("Expenses",A1:D4,2) will result in a #NAME error because "Expenses" is not a
horizontal lookup reference.
=HLOOKUP(2016,B2:D4,2) will reference the Revenue line rather than the expenses line because
the third argument references the row 2 in the table array.
Identify the commands to insert a comment and delete a comment
1)Alt e m 2) Alt e m
1)Alt h e m 2) Alt I m
1)Shift F2 2) Alt h e m
1)Alt h e m 2) Alt I s
- ANSWERS-The correct answer is Shift F2, Alt h e m.
Alt e m is a shortcut to open the menu to move or copy a selected sheet. Alt I s is the shortcut to
insert a symbol.
Identify the formula that outputs 2016 expenses.
=VLOOKUP("Expenses",A1:D4,3,1)
=VLOOKUP("Expenses",A1:D4,3)
=VLOOKUP(2016,A1:D4,3)
=VLOOKUP("Expenses",A1:D4,3,0)
- ANSWERS-The correct answer is: =VLOOKUP("Expenses",A1:D4,3,0)
=VLOOKUP("Expenses",A1:D4,3,1) looks for an approximate match and requires that the
reference range be in ascending order.
=VLOOKUP("Expenses",A1:D4,3) also looks for an approximate match as specified by match
type 1, and requires that the reference range be in ascending order.
Identify the formula that outputs data from the table based on the user inputs in cells C6 and C7.
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1))
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1))
=OFFSET(A1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1))
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0))
- ANSWERS-The correct answer is: =OFFSET(A1, MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)).
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1)) does not return the correct value because an
exact match is not specified for the MATCH function used for the 'row' argument of the OFFSET
function.
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)) uses an incorrect cell reference for the
reference argument of the OFFSET function, which must be in the upper left corner of the table.
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0)) reverses the MATCH functions used for the
row and column references in the OFFSET function, returning #N/A.
Identify the formula that outputs the sum of property values for properties in Boston that were
built after 1950.
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
=SUMIFS(C2:C5,"Boston",D2:D5,">1950",B2:B5)
=SUMIF(B2:B5,and(D2:D5,">1950",C2:C5,"Boston")
- ANSWERS-The correct answer is: =SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
