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 - ansAlt 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 -
ansThe 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) - ansThe 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) - ansThe 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 - ansThe 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) - ansThe 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)) - ansThe 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") - ansThe correct answer is:
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
=SUMIFS(C2:C5,"Boston",D2:D5,">1950",B2:B5) selects column C instead
of Column B as the sum range
=SUMIF(B2:B5,and(D2:D5,">1950",C2:C5,"Boston") incorrectly selects
column D instead of column B as the sum range
, Identify the formula that sums the property values of properties in Boston.
=SUMIF(B2:B5,"Boston",C2:C5)
=SUMIF(C2:C5,"Boston",B2:B5)
=SUMIF(C2:C5,Boston,B2:B5) - ansThe correct answer is:
=SUMIF(C2:C5,"Boston",B2:B5)
=SUMIF(B2:B5,"Boston",C2:C5) does not use the correct range for matching
with the sum criteria
=SUMIF(C2:C5,Boston,B2:B5) does not use quotes for "Boston", which will
return an error
Identify the formula that will always output a date that is the end-of-month
date 3 months after the date inputted in D5.
=DATE(YEAR(D5),MONTH(D5)+3,DAY(D5))
=EOMONTH(3,D5)
=EOMONTH(D5,3) - ansThe correct answer is: =EOMONTH(D5,3)
=DATE(YEAR(D5),MONTH(D5)+3,DAY(D5)) will add 3 months to the data
inputted in D5. EOMONTH(3,D5) reverses the arguments required for this
function.
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) - ansThe 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) - ansThe 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, based in user inputs in cells B1 and B2,
outputs the text "animal lover" for users who have at least 1 dog and
at least one cat, and outputs "lonely person" when those conditions
are not met.
=IF(OR(B1>0,B2>0),"animal lover","lonely person")
=IF(AND(B1>1,B2>1),"animal lover","lonely person")
=IF(AND(B1>0,B2>0),"animal lover","lonely person")
=AND(IF(B1>0,"animal lover","lonely person"),IF(B2>0,"animal
lover","lonely person")) - ansThe correct answer is:
=IF(AND(B1>0,B2>0),"animal lover","lonely person")
=IF(OR(B1>0,B2>0),"animal lover","lonely person") will output "animal
lover" if the user owns a dog OR a cat.
more sheets to a workbook EXCEPT:
Alt i w
Alt h i s
Shift
F11
Alt Shift F1
Alt h i w
Incorrect - ansAlt 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 -
ansThe 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) - ansThe 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) - ansThe 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 - ansThe 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) - ansThe 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)) - ansThe 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") - ansThe correct answer is:
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
=SUMIFS(C2:C5,"Boston",D2:D5,">1950",B2:B5) selects column C instead
of Column B as the sum range
=SUMIF(B2:B5,and(D2:D5,">1950",C2:C5,"Boston") incorrectly selects
column D instead of column B as the sum range
, Identify the formula that sums the property values of properties in Boston.
=SUMIF(B2:B5,"Boston",C2:C5)
=SUMIF(C2:C5,"Boston",B2:B5)
=SUMIF(C2:C5,Boston,B2:B5) - ansThe correct answer is:
=SUMIF(C2:C5,"Boston",B2:B5)
=SUMIF(B2:B5,"Boston",C2:C5) does not use the correct range for matching
with the sum criteria
=SUMIF(C2:C5,Boston,B2:B5) does not use quotes for "Boston", which will
return an error
Identify the formula that will always output a date that is the end-of-month
date 3 months after the date inputted in D5.
=DATE(YEAR(D5),MONTH(D5)+3,DAY(D5))
=EOMONTH(3,D5)
=EOMONTH(D5,3) - ansThe correct answer is: =EOMONTH(D5,3)
=DATE(YEAR(D5),MONTH(D5)+3,DAY(D5)) will add 3 months to the data
inputted in D5. EOMONTH(3,D5) reverses the arguments required for this
function.
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) - ansThe 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) - ansThe 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, based in user inputs in cells B1 and B2,
outputs the text "animal lover" for users who have at least 1 dog and
at least one cat, and outputs "lonely person" when those conditions
are not met.
=IF(OR(B1>0,B2>0),"animal lover","lonely person")
=IF(AND(B1>1,B2>1),"animal lover","lonely person")
=IF(AND(B1>0,B2>0),"animal lover","lonely person")
=AND(IF(B1>0,"animal lover","lonely person"),IF(B2>0,"animal
lover","lonely person")) - ansThe correct answer is:
=IF(AND(B1>0,B2>0),"animal lover","lonely person")
=IF(OR(B1>0,B2>0),"animal lover","lonely person") will output "animal
lover" if the user owns a dog OR a cat.
