EXCEL CRASH COURSE ACTUAL FINALS ANSWERS AND QUESTIONS SURE A.pdf

EXCEL CRASH COURSE ACTUAL FINALS ANSWERS
AND QUESTIONS SURE A+
✔✔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) - ✔✔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 formula that will output 2016 expenses (55).
=OFFSET(B1,3,2)
=OFFSET(B1,2,3)
=OFFSET(A1,2,2)
=OFFSET(A1,3,3) - ✔✔The correct answer is: =OFFSET(A1,2,2)
=OFFSET(B1,3,2) will return 65.
=OFFSET(B1,2,3) will return (75).
=OFFSET(A1,3,3) will return 55.

✔✔Identify the formula that will output 2016 expenses:
=CHOOSE(2,B3:D3)
=CHOOSE(2,B3,C3,D3)
=CHOOSE(2,A3,B3,C3,D3) - ✔✔The correct answer is: =CHOOSE(2,B3,C3,D3)
=CHOOSE(2,B3:D3) is not valid syntax for this formula; cells must be individually
selected.
=CHOOSE(2,A3,B3,C3,D3) will output 2015 expenses.

✔✔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) - ✔✔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)) - ✔✔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.

✔✔What INDEX & MATCH combo will create a dynamic reference based on whatever
metric and date you input in cells F10 and F11, respectively?

=INDEX(D6:H8,MATCH(F10,C6:C8,0),MATCH(F11,D5:H5,0))
=INDEX(D6:H8,MATCH(F10,C6:C8,1),MATCH(F11,D5:H5,0))
=INDEX(D6:H8,MATCH(F10,C6:C8,1),MATCH(F11,D5:H5,1))
=INDEX(D6:H8,MATCH(F10,C6:C8,0),4) - ✔✔The correct answer is:
=INDEX(D6:H8,MATCH(F10,C6:C8,0),MATCH(F11,D5:H5,0)).
=INDEX(D6:H8,MATCH(F10,C6:C8,1),MATCH(F11,D5:H5,0)) uses match type 1 for the
first argument, which requires values to be in ascending order, and is not valid for this
reference.
=INDEX(D6:H8,MATCH(F10,C6:C8,1),MATCH(F11,D5:H5,1)) uses match type 1 for
both MATCH function, which requires values to be in ascending order, and is not valid
for this reference.
=INDEX(D6:H8,MATCH(F10,C6:C8,0),4) selects the fourth column instead of the fifth,
and is static; it will not change if a different date is entered.

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