Microsoft Word - Excel Introduction to Formuals



Excel:IntroductiontoFormulas

TableofContents



Formulas

Arithmetic&ComparisonOperators..................................................................................................2

TextConcatenation.............................................................................................................................2

OperatorPrecedence..........................................................................................................................2

UPPER,LOWER,PROPERandTRIM.....................................................................................................3

&(Ampersand)....................................................................................................................................4

SUM.....................................................................................................................................................5

ROUND................................................................................................................................................5

COUNT.................................................................................................................................................6

IF..........................................................................................................................................................7

Anchoringrows/columnswith$sign..................................................................................................7

CombiningFormulasFrom

Multipleworksheets................................................................................8



PracticeSet........................................................................................................................................................9







2



Formulas

Arithmetic&ComparisonOperators

Operator

Meaning Example Result

+

Addition A1+B1 NumericValue

‐

SubtractionorNegative A1‐B1

NumericValue

*

Multiplication A1*B1

NumericValue

/

Division A1/B1

NumericValue

=

Equalto A1=B1 LogicalValue(TRUEorFALSE)

>

Greaterthan A1>B1 LogicalValue(TRUEorFALSE)

<

Lessthan A1<B1 LogicalValue(TRUEorFALSE)

>=

Greaterthanorequalto A1>=B1 LogicalValue(TRUEorFALSE)

<=

Lessthanorequalto A1<=B1 LogicalValue(TRUEorFALSE)

<>

Notequalto A1<>B1 LogicalValue(TRUEorFALSE)



TextConcatenationOperators

Operator

Meaning Example Result

&

Connects,or

concatenates,

multiple

valuesto

produceone

continuous

textvalue



Wanttocombinethevaluesin

columnsA‐C.Iaddedaspace,via

thespacebar,sothewordswould

haveaspacebetweenthem.



Theshowswhattheformulain

D1lookslike.Youcanseethevalue

inD1hasthetwowordscombined

nicely.



OperatorPrecedence



Ifyoucombineseveraloperatorsinasingleformula,Excelperformstheoperationsinaspecificorder,

describedbelow.IfoperatorswithinthesameformulasharethesameprecedenceExcelthendefaults



3



fromlefttoright.Theusermaychangetheorderbywhichcalculationsareperformedbyusing

parentheses.



Thefollowingisanexampleofwhytheprecedenceneedstobeunderstoodandwhyitisimportant:



Formula

Result Calculation

=5+2*3

11 (2times3)plus5

=(5+2)*3 21 (5plus2)times3



OperatorPrecedence

Operator

Meaning

*and/

Multiplication andDivision

+and‐

AdditionandSubtraction

&

TextConcatenation

=

Equalto

<>

Notequalto

<=

Lessthanorequalto

>=

Greaterthanorequalto



UPPER,LOWER,PROPER,andTRIM

Theseformulasallworkwithtext.Afterusingoneofthesefunctionsitisgoodpracticetopaste

special\valuessothattheywillremainintheirdesiredformatting.



4



UPPER,LOWER,PROPER,andTRIM

Formula

Description

=UPPER

Convertsalltexttouppercase

=LOWER

Convertsalltexttolowercase

=PROPER

Capitalizesthefirstletterinatextstringandanyotherlettersin

textthatfollowanycharacterotherthanaletter,i.e.aspace.

Convertsallotherletterstolowercase

=TRIM

Removesallblank,unnecessaryspacesatthestartandendofa

stringincludingextraspaces,tabs,andothercharactersthat

don’tprint.



&(Ampersand)

The&connects,orconcatenates,multiplevaluestoproduceonecontinuoustextvalue.Afterusingthis

functionitisgoodpracticetopastespecial\valuessothattheywillremainintheirdesiredformatting.

ThefinishedproductIwantistohaveShastaCountyinonecellwhichIcan

accomplishwiththe&

function.BycombiningthevaluesincolumnsAandBIhaveaccomplishedmydesiredtask,butquite

literally.NotethereisnospacebetweenthetwowordsincellC1.



ByaddingacolumntotherightofcolumnAandpressingthespace

baronce,creatingasinglespace,and

modifyingmyformulatonowincludecolumnsA–C, Inowhaveamorereadableresult.



Noteifyourdataconsistsof

severalrowsyouwould

needtocopytheblank

spaceinB1allthewayto

thelastrow.



Noticethereisnospace

betweenthetwowords.



5



SUM



TheSUMfunctionisthesingularlymostusedfunctionwithinExcel.Itisusedtototalvaluesinyour

worksheets.Thesevaluesmaybecontinuous,noncontinuous,fromdifferentworksheets,etc,oravariety

thereof.

Thesyntaxis=SUM(number1,[number2],[...]) 

Anexampleoftheformulais=SUM(A1:A4).TheEnglishtranslationisaddupallofthevaluesfoundinthe

rangeofbetweenA1andA4,inclusive,anddisplaystheresult.



NoticethatIhaveoneextralinewithinmyformula.Ido

thatonallofmyformulasasabestpractice.IfI

needtoaddanyadditionalrows,bydoingsoabovetheblankrow,Iamensuredmyformulawill properly

bemodifiedautomatically.

Therearemanyvariationstothisformula,thisisjustoneexample.



ROUND

TheROUNDfunction

roundsanumbertoaspecifiednumberofdigits.Thissho uldnotbeconfusedwith

formattingtoaspecifieddecimalplaces.

Thesyntaxis=ROUND(number,num_digits)

ExpandingourpreviousSUMformulafromabove,theformulais=ROUND(SUM(A1:A4),2).TheEnglish

translationisaddupallofthevaluesfoundintherangeofbetweenA1andA4,inclusive,roundtheresult

totwodecimalplaces,anddisplaytheresult.

isimportantnottoconfuseroundingtoaspecificnumberofdecimalsandformattingyourcelltoa

specificnumberofdecimals.Forexample,ifcellA5belowcontains18.44978.Ifweweretoformatthecell

totwodecimalplaces,18.45willbedisplayed.However,Excelstillseesit

as18.44978(Beforepicture).IfI

Addupthevaluesinthisrange

Andplacetheresulthere



6



wantExceltosee,anduseinsubseq uentcalculations,18.45Iwouldneedtohavethefollowing rounding

formulainA5:=ROUND(SUM(A1:A4),2)(Afterpicture)

WithoutROUNDFormula

WithROUNDFormula



COUNT 

TheCOUNTfunctioncountsthenumberofcellsthatcontainnumbersandcountsnumberswithinthelist

ofarguments.

ThesyntaxisCOUNT(value1,value2,…)

ContinuingonwithourSUMformulafromabove,let’snotonlyaddupthevaluesoftherangeA1:A4,but

let’scounthowmanynumbersareincludedwithintherange,i.e.howmanycellswithintherangehasa

valueinit.

Theformulais=COUNT(A1:A4).TheEnglishtranslationiscounthowmanycellswithintherangehasa

valueinitanddisplaytheresult.



Noticethattherangeisexactlythesameasour

SUM,A1:A4,whichincludesfourrows.Thevalue

returnedincellA7isthree,becauseonlythreeofthe

fourrowshavevaluesinthem.



Ifyouaretryingtocounttext,usetheCOUNTAformulawhichcountsthe

non‐blankcells.



7



IF

Theformulamakesastatement/question,iftheansweristruethenoneresponseisobtained.Ifthe

answeriffalse,thenanotheranswerisobtained.

Thesyntaxis=IF(logical_test,value_if_true,value_if_false)

ContinuingonwithourSUMformulafromabove,let’saddsomeverbagetoemphasizewhethertheresult

isgreateror

lessthantwenty.

Theformulais=if(A5<20,”Amountislessthantwenty”,”Amountismorethantwenty”).TheEnglish

translationisifthevaluefoundinA5is lessthantwentyTHENdisplaythecomment‘Amountislessthan

twenty’ELSEdisplaythecomment‘Amountismorethantwenty’.



AnchoringRowsandColumnsWith$Sign

Asformulasarecopiedeitherthecolumnreferenceincreasesortherownumberdependingonthe

directionofthecopy.Ifcopyingtotherightthroughthespreadsheet,thecolumnreferencewillincrease;

ifcopyingdownthroughthespreadsheet,therowreferenceswill

increase.

Inordertooverruletheautomaticincrement,placeadollarsigninfrontofthereferencethatyoudon’t

wanttochange,thecolumn,row,orboth.

AnchoringRowsandColumnsWith$Sign

Source

Formula

Action

Destination

Formula Effect

=SUM(A1:A4) Copyformulaonecell

totheright

=SUM(B1:B4) Columnreferences

increasedfromAtoB

andAtoB

=SUM($A1:A4) Copyformulaonecell

totheright

=SUM($A1:B4) ColumnreferencesA

stayedconstantatAand

increasedfromAtoB

=SUM(A1:A4) Copyformulaonecell

down

=SUM(A2:A5) Rowreferences

increasedfrom1to2

and4to5



8



=SUM(A$1:A4) Copyformulaonecell

down

=SUM(A$1:A5) Rowreferences1stayed

constantat1and

increasedfrom2to5

=SUM($A$1:$A$4) Copyformula

anywherewithinthe

spreadsheet

=SUM($A$1:$A$4) Neithercolumnnorrow

referenceschanged



CombiningFormulasBetweenMultipleWorksheets

Datacanbepulledfromotherworksheetsandutilizedonothers.Thisfunctioncanbeusedforboth

numericalandtextdata.Theformulascancombineonetomanyworksheetsareranges.

ForExample,thisisextremelyhandywhenoneworksheetactsasasummaryand

recapsinformationfrom

thedetailworksheets.Ourexamplebelowrecapssalesononesheet,whilethemonthlydetailin

maintainedonothersheets.

NotetheworksheetnamesofSummary,Jan,Feb,&Mar.WeareworkingwithintheSummaryworksheet,

denotedbythetabcolor.Thecurserisincell

D6whichreceivesitsinformationfromtheJanuary

worksheet.







9



PRACTICESET

Usingthedataonthestaffmileagedatatab,performthefollowingsteps:

1. Insertrowsandaddtheheader.Changethefontsizeto12.Makeboldanditalize.

2. Bold,underline,wordwrap,andcenterheaders.

3. Sortemployeedatalines,skippingthebudgetrowbyemployee

nameanddate

4. UsingthePROPERcommandcleanuptheemployeesnames.

5. UsingtheSUMformulaaddtotalstotheadopted,revised,andactualcolumns.

Addthetopandbottombordertothesums.

AddREMAININGBALANCEtextanddoabasicsubtractionformulacalculatingthedifference

betweentherevisedbudgettotalandtheactualtodate.



Add‘NUMBEROFTRANSACTIONSTODATE’caption.UsingtheCOUNTformulacountthenumber

oftransactions.



9. Addanewcolumnentitled‘RemainingBalance(RevisedvsActual).Usingbasicsubtraction

calculatetheremainingbalanceonaperlinebasis.



10.

Yourfinalproductshouldlooklikethis:

