Transcription
numbers,etc.inyourworkbooks.FormulaOperations: Addition 1 1- ‐Subtraction 1- ‐1*Multiplication 3*3/Division 6/2 Exponent 3 4%Percent 20%(Dividesby100) fOperations1.Parentheses(Everythinginparentheses) 1 1(2 2)2.Percent 1 1/20%3.Exponents 1 1*2 34.Division/Multiplication 1 1 2*35.Addition/Subtraction 1 1Relativevs.AbsoluteRelative A1InformationcanchangeAbsolute A 1InformationcannotchangeMixed eedtokeep 1.50usethe intheformula.Functionsarethebuilt- mtop- ‐lefttobottom- ontotwoormoredifferencecellranges.B1:B3C4:C61
ll.3.ClickFormulas- ‐ cell,type: nkcell,type: IF(D1 han100.Exercise2:1.Inanyblankcell,type: IF(D1 100,"A",IF(D2 200,"B",IF(D3 300,"C",IF(D4 40inCellD1.4.ChangeD1to14andD2to411.2
oanIFstatement.Exercise1:1.Inanyblankcell,type: IF(AND(D1 100,D2 irvalues.6.Select2- Type: SUMIF(C7:C10,"Jewel",B7:B10)3
,type: SUMIFS(B3:B18,A3:A18," ayworksheet.2.Type usedowntoD7.Yourformulashouldlooklike ntoE7.Yourformulashouldlooklike l Shift orunthearray.Yourformulashouldnowlooklike:{ uwanttoflip.2.IncellB24,type: e.5.Typethe)toclosetheformula.6.PressCntl Shift Enter.Theformulashouldlooklike:{ ray.4
dsheetandclickincellC11:5
.IntheLookup valuefield,selectthecellwithrevenueamount.6
IntheTable sojusttypeA3:C7intheTable mportant:The“Table enteringa kupfunctionsmustuseAbsolutefunctions.IntheTable arrayfield,changethedatato: A 3: C 77
IntheCol index SelectOKintheFunctionArgumentsbox.8
willtypehereis: he ab- ‐ ghub.com/excel- ‐offset- ‐function- hintheVLOOKUP.(Checkyourspelling!)9
ncellI15.Theformulawewilluseis: :1. atarecommonwiththesetypesofformulas.PV- ‐PresentValue- FV- ‐FutureValue- nthefuture.Rate- T- ‐Payment- nt.10
NPER- ‐NumberofPayments- seisIPMT. onwewilluseis: PPMTTheformulawewilluseis: ymonth.Thefunctionwewilluseforthisformulais:PMT. PMT(4.3%/12,360,250000)11
et,theNPVtableissetupwithanETFwitha3%ratewitha ounting”intheHomeTabintheRibbon.Incell:Year1:- ollows: NPV(3%,B19,C19,D19) forthevalueofthemortgageis: 20RetirementAge:65AnnualPaymentstoIRA: 2000withrateofaveragerateofreturn3%Formula: FV(3%,45,- Awithyournewjob.Let’sassumeyourollover 10500.Theformulawillchange: FV(3%,45,- ‐2000,- is0.12
ion.Stra
1" "!!Advanced!Excel! Formulas!:!Functions!! " ur"experience"with"the"program"to"anew" level."Formulas"are"the"basic .
