EXCEL CRASH EXAMS WITH VERIFIED
a a a a
QUESTIONS AND CORRECT
a a a a
ANSWERS a
All aof athe afollowing aare akeyboard ashortcut athat aallow athe auser ainput ato aadd amore
asheets ato aa a workbook aEXCEPT:
Alt ai aw
a Alt ah ai
as a Shift
aF11
Alt aShift aF1
a Alt ah ai aw
a Incorrect
- aANSWERS-Alt ai aw, aAlt ah ai as, aShift aF11 aand aAlt aShift aF1 aare aall akeyboard ashortcuts
afor aadding a more asheets ato aa aworkbook.
Alt ah ai aw ais anot aa avalid ashortcut.
Below ais aa aData aTable adesigned ato adisplay athe aeffect aon a2015 aNet aProfit abased aof
achanges a in a 2015 aRevenue aGrowth aand a2015 aCOGS a%. aPlease aprovide:
The acell areference arequired ain aD22.
The arow ainput acell arequired aby athe aData
aTable. a The acolumn ainput acell arequired aby
athe aData aTable.
Provide aall athree acell areferences, ain aorder aabove, aseparated aby acommas awith ano
aspace a(i.e. a A1,A2,A3) aNote: aDo anot ainclude aspaces awithin aanswer
- aANSWERS-The acorrect aanswer ais: aE8,E10,E11
E8 ais athe acell areference afor a2018 aNet aIncome, athe aoutput avariable, awhich amust abe ain
athe atop aleft a corner aof athe atable. aE10, athe a2015 arevenue agrowth arate, ais athe arow
ainput acell, athe avariable athat ais a being asensitized ain athe atop arow aof athe atable. aE11, athe
aCOGS a%, ais athe acolumn ainput avariable athat ais a being asensitized.
Identify aa afunction ain acell aD6 athat awill areturn athe afraction aof athe ayear aelapsed
aassuming aa a360 aday a count abasis.
=STUB(D4,D5)
=YEARFRAC(D4,D5,2)
=DAYS360(D4,D5)
=YEARFRAC(D4,D5)
- aANSWERS-The acorrect aanswer ais: a=YEARFRAC(D4,D5,2). aSTUB ais anot aan aExcel
aformula. aDAYS360 a will areturn athe anumber aof adays ain aa a360 aday ayear.
aYEARFRAC(D4,D5) awill acalculate athe afraction a using aa a365-day ayear.
Identify athe abest aformula athat awill aoutput a2016 aexpenses. aHint: aOnly aselect athe a'range
,lookup' a argument aif ait ais anecessary.
a
=HLOOKUP(2016,B2:D4,2,0)
=HLOOKUP(2016,A1:D4,3)
=HLOOKUP("Expenses",A1:D4,2)
, =HLOOKUP(2016,B2:D4,2)
- ANSWERS-The acorrect aanswer ais: a=HLOOKUP(2016,A1:D4,3)
=HLOOKUP(2016,B2:D4,2,0) awill anot aresult ain aa avalid aoutput abecause a2016 ais anot
aselected ain athe a table aarray aargument.
=HLOOKUP("Expenses",A1:D4,2) awill aresult ain aa a#NAME aerror abecause a"Expenses"
ais anot aa a horizontal alookup areference.
=HLOOKUP(2016,B2:D4,2) awill areference athe aRevenue aline arather athan athe aexpenses
aline abecause a the athird aargument areferences athe arow a2 ain athe atable aarray.
Identify athe acommands ato ainsert aa acomment aand adelete aa
acomment a 1)Alt ae am a2) aAlt ae am
1)Alt ah ae am a2) aAlt aI am
a 1)Shift aF2 a2) aAlt ah ae
am a 1)Alt ah ae am a2) aAlt
aI as
- ANSWERS-The acorrect aanswer ais aShift aF2, aAlt ah ae am.
Alt ae am ais aa ashortcut ato aopen athe amenu ato amove aor acopy aa aselected asheet. aAlt aI as ais
athe ashortcut ato a insert aa asymbol.
Identify athe aformula athat aoutputs a2016 aexpenses.
=VLOOKUP("Expenses",A1:D4,3,1)
=VLOOKUP("Expenses",A1:D4,3)
=VLOOKUP(2016,A1:D4,3)
=VLOOKUP("Expenses",A1:D4,3,0)
- aANSWERS-The acorrect aanswer ais: a=VLOOKUP("Expenses",A1:D4,3,0)
=VLOOKUP("Expenses",A1:D4,3,1) alooks afor aan aapproximate amatch aand arequires athat
athe a reference arange abe ain aascending aorder.
=VLOOKUP("Expenses",A1:D4,3) aalso alooks afor aan aapproximate amatch aas aspecified
aby a match a type a1, aand arequires athat athe areference arange abe ain aascending aorder.
Identify athe aformula athat aoutputs adata afrom athe atable abased aon athe auser ainputs ain acells
aC6 aand aC7.
=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))
- aANSWERS-The acorrect aanswer ais: a=OFFSET(A1,
aMATCH(C6,A2:A4,0),MATCH(C7,B1:D1)).
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1)) adoes anot areturn athe acorrect
avalue abecause aan a exact amatch ais anot aspecified afor athe aMATCH afunction aused afor
athe a'row' aargument aof athe aOFFSET a function.
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)) auses aan aincorrect acell
areference afor athe a reference aargument aof athe aOFFSET afunction, awhich amust abe ain
athe aupper aleft acorner aof athe atable.
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0)) areverses athe aMATCH
afunctions aused afor athe a row aand acolumn areferences ain athe aOFFSET afunction,
areturning a#N/A.
Identify athe aformula athat aoutputs athe asum aof aproperty a values afor aproperties ain
aBoston athat awere a built aafter a1950.
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
a a a a
QUESTIONS AND CORRECT
a a a a
ANSWERS a
All aof athe afollowing aare akeyboard ashortcut athat aallow athe auser ainput ato aadd amore
asheets ato aa a workbook aEXCEPT:
Alt ai aw
a Alt ah ai
as a Shift
aF11
Alt aShift aF1
a Alt ah ai aw
a Incorrect
- aANSWERS-Alt ai aw, aAlt ah ai as, aShift aF11 aand aAlt aShift aF1 aare aall akeyboard ashortcuts
afor aadding a more asheets ato aa aworkbook.
Alt ah ai aw ais anot aa avalid ashortcut.
Below ais aa aData aTable adesigned ato adisplay athe aeffect aon a2015 aNet aProfit abased aof
achanges a in a 2015 aRevenue aGrowth aand a2015 aCOGS a%. aPlease aprovide:
The acell areference arequired ain aD22.
The arow ainput acell arequired aby athe aData
aTable. a The acolumn ainput acell arequired aby
athe aData aTable.
Provide aall athree acell areferences, ain aorder aabove, aseparated aby acommas awith ano
aspace a(i.e. a A1,A2,A3) aNote: aDo anot ainclude aspaces awithin aanswer
- aANSWERS-The acorrect aanswer ais: aE8,E10,E11
E8 ais athe acell areference afor a2018 aNet aIncome, athe aoutput avariable, awhich amust abe ain
athe atop aleft a corner aof athe atable. aE10, athe a2015 arevenue agrowth arate, ais athe arow
ainput acell, athe avariable athat ais a being asensitized ain athe atop arow aof athe atable. aE11, athe
aCOGS a%, ais athe acolumn ainput avariable athat ais a being asensitized.
Identify aa afunction ain acell aD6 athat awill areturn athe afraction aof athe ayear aelapsed
aassuming aa a360 aday a count abasis.
=STUB(D4,D5)
=YEARFRAC(D4,D5,2)
=DAYS360(D4,D5)
=YEARFRAC(D4,D5)
- aANSWERS-The acorrect aanswer ais: a=YEARFRAC(D4,D5,2). aSTUB ais anot aan aExcel
aformula. aDAYS360 a will areturn athe anumber aof adays ain aa a360 aday ayear.
aYEARFRAC(D4,D5) awill acalculate athe afraction a using aa a365-day ayear.
Identify athe abest aformula athat awill aoutput a2016 aexpenses. aHint: aOnly aselect athe a'range
,lookup' a argument aif ait ais anecessary.
a
=HLOOKUP(2016,B2:D4,2,0)
=HLOOKUP(2016,A1:D4,3)
=HLOOKUP("Expenses",A1:D4,2)
, =HLOOKUP(2016,B2:D4,2)
- ANSWERS-The acorrect aanswer ais: a=HLOOKUP(2016,A1:D4,3)
=HLOOKUP(2016,B2:D4,2,0) awill anot aresult ain aa avalid aoutput abecause a2016 ais anot
aselected ain athe a table aarray aargument.
=HLOOKUP("Expenses",A1:D4,2) awill aresult ain aa a#NAME aerror abecause a"Expenses"
ais anot aa a horizontal alookup areference.
=HLOOKUP(2016,B2:D4,2) awill areference athe aRevenue aline arather athan athe aexpenses
aline abecause a the athird aargument areferences athe arow a2 ain athe atable aarray.
Identify athe acommands ato ainsert aa acomment aand adelete aa
acomment a 1)Alt ae am a2) aAlt ae am
1)Alt ah ae am a2) aAlt aI am
a 1)Shift aF2 a2) aAlt ah ae
am a 1)Alt ah ae am a2) aAlt
aI as
- ANSWERS-The acorrect aanswer ais aShift aF2, aAlt ah ae am.
Alt ae am ais aa ashortcut ato aopen athe amenu ato amove aor acopy aa aselected asheet. aAlt aI as ais
athe ashortcut ato a insert aa asymbol.
Identify athe aformula athat aoutputs a2016 aexpenses.
=VLOOKUP("Expenses",A1:D4,3,1)
=VLOOKUP("Expenses",A1:D4,3)
=VLOOKUP(2016,A1:D4,3)
=VLOOKUP("Expenses",A1:D4,3,0)
- aANSWERS-The acorrect aanswer ais: a=VLOOKUP("Expenses",A1:D4,3,0)
=VLOOKUP("Expenses",A1:D4,3,1) alooks afor aan aapproximate amatch aand arequires athat
athe a reference arange abe ain aascending aorder.
=VLOOKUP("Expenses",A1:D4,3) aalso alooks afor aan aapproximate amatch aas aspecified
aby a match a type a1, aand arequires athat athe areference arange abe ain aascending aorder.
Identify athe aformula athat aoutputs adata afrom athe atable abased aon athe auser ainputs ain acells
aC6 aand aC7.
=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))
- aANSWERS-The acorrect aanswer ais: a=OFFSET(A1,
aMATCH(C6,A2:A4,0),MATCH(C7,B1:D1)).
=OFFSET(A1,MATCH(C6,A2:A4),MATCH(C7,B1:D1)) adoes anot areturn athe acorrect
avalue abecause aan a exact amatch ais anot aspecified afor athe aMATCH afunction aused afor
athe a'row' aargument aof athe aOFFSET a function.
=OFFSET(B1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1)) auses aan aincorrect acell
areference afor athe a reference aargument aof athe aOFFSET afunction, awhich amust abe ain
athe aupper aleft acorner aof athe atable.
=OFFSET(A1,MATCH(C7,B1:D1),MATCH(C7,A2:A4,0)) areverses athe aMATCH
afunctions aused afor athe a row aand acolumn areferences ain athe aOFFSET afunction,
areturning a#N/A.
Identify athe aformula athat aoutputs athe asum aof aproperty a values afor aproperties ain
aBoston athat awere a built aafter a1950.
=SUMIFS(B2:B5,C2:C5,"Boston",D2:D5,">1950")
