LeanExcel:TopFunctions

ByScottRatliff

www.LeanExcelBooks.com

LegalStuff

Copyright©2015byScottRatliff

Allrightsreserved.Nopartofthisebookmaybereproducedortransmittedinanyform
orbyanymeanswithoutwrittenpermissionfromthepublisher.

Allbrandnamesandproductnamesusedinthisbookaretradenames,servicemarks,
trademarks,orregisteredtrademarksoftheirrespectiveowners.ScottRatliffisnot
associatedwithanyproductorvendormentionedinthisbook.

Introduction

Knowinghowtousethebuilt-infunctionsinMicrosoftExcelwillturnyouintoa

poweruser!Therearehundredsoffunctionsandatfirst,thelistcanseemoverwhelming.
EvenasaMicrosoftOfficeExpertinExcel2013IdonotknowalloftheExcelfunctions
available.InwritingthiseBook,IdiscoveredseveralnewfunctionsthatIhavenow
incorporatedintomydailyuseandhavemademydataanalysismoreefficient.Ihopethat
thiseBookwillhelpyoulearnmoreoftheExcelfunctionsandthattheywouldhelpyou
gaincontroloveryourspreadsheets!

WhyLeanExcel?

ThisLeanExcelseriesfocusesontime-savingtoolsthatwillhelpyoureducethe

wasteassociatedwithdataanalysis.Oneofthetenetsofleanmanufacturingtheoryis
usingspecializedtoolstoaccomplishatask.Youcoulduseawrenchtodriveanailbuta
hammerwillgetthejobdonemuchfaster.Remembertimeismoney.

Thinkofthebuilt-infunctionsasspecializedtools.Let’ssayyouneededtofindthe

averageofadatasetthatisinA1throughA5.Remembertheaverageisthesumofallthe
numbersinadatasetdividedbythecountofnumbersinthatdataset.Let’slookatthree
waystofindtheaverage.First,youcouldusetheformula=(A1+A2+A3+A4+A5)/5.
Second,youcouldusetheSUMandCOUNTfunctions=SUM(A1:A5)/COUNT(A1:A5).
Orlastly,youcouldusetheAVERAGEfunction=AVERAGE(A1:A5).Althoughallthree
optionsgiveyouthesameanswer,usingtheAVERAGEfunctionisclearlythemost
efficient.ButinordertousetheAVERGEfunction,youneedtoknowthatitexistsand
whatrequirementsyouneedtouseit.

EnteringFormulasandFunctions

First,let’sidentifythesimilaritiesbetweenformulasandfunctions.Bothformulas

andfunctionscanbeenteredusingtheformulabarimmediatelybelowtheExcelRibbon.
TheExcelRibbonisthehorizontalbarnearthetopoftheExcelwindowthatcontainsall
ofthebuttonsfortoolsavailableinExcel.Bothformulasandfunctionsbeginwiththe
equalssign(=).Thisisrequiredonlyatthebeginningoftheformulabar;itisnotrequired
beforeeachindividualfunctionwithinthatcell.TheequalssigntellsExcelthatyouwant
Exceltodoacalculationforthiscell.Ifyoudidnothavetheequalssign,thenExcelwill
putexactlywhatyoutypeintothatcell.

Next,let’sidentifythedifferencebetweenafunctionandaformula.Inthefirst

sectionofthisbook,wediscussed3waystofindtheaverageoffivenumbers.Thefirst
optionisanexampleofaformula.Itbeginswithanequalssign(=)andincludes
mathematicoperators(addition:+,subtraction:-,multiplication:*,division:/).Asseenin
thefirstoption=(A1+A2+A3+A4+A5)/5,itcanincludebothreferencestocells(A1,A2,
A3,etc.)andactualnumbers(the5afterthedivisionsign).Aformulacanincludeother
mathematicoperatorssuchasparentheses()todeterminetheorderofoperationsand^for
exponents.Excelfollowsthesameorderofoperationsyoulearnedinalgebra:
Parenthesesthenexponentsthenmultiplicationanddivisionandlastlyadditionand
subtraction.Soifyouentered=(2+3)^2/5+1intoacellinExcel,itwouldfirstdowhat
wasintheparentheses(2+3)=5.Nextitwouldlookforexponentsso(5)^2=25.Thenit
wouldlookformultiplicationordivision25/5=5andlastlyadditionorsubtraction5+1=
6.So6wouldbetheresultoftheformulaandbedisplayedinthecell.

Afunctionisenteredverysimilarlybutusesabuilt-inkeywordthatExcelwill

recognize.ThethirdoptionfromtheprevioussectionusestheAVERAGEfunction.To
enterthefunction,firstputanequalssign(=)thentypethefunctionname.Excelwilltry
torecognizethekeywordyouaretypingandprovideyoualistofthefunctionsthatbegin
withwhatyoutyped.Youcaneithercontinuetypingordouble-clickonthefunctionyou
seeinthelist.Whenyoutypethekeyword,uppercaseisnotrequired;however,ifExcel
identifiesthekeywordasafunction,thenitwillconvertittouppercase.Alternatively,
youcouldclickontheFormulatabontheExcelribbonandchooseafunctionfromthe
categorieslistedthere.Forreferenceconsistency,thefunctionsincludedinthiseBookare
dividedtomatchthecategoriesontheFormulastaboftheExcelribbon.

Eachfunctionbeginswiththeequalssign(=)thenthekeyword(AVERAGE,

COUNT,SUM,etc.)andthenanopenparentheses(.Aftertheopenparentheses,Excel
expectsalistofarguments.Thisisthemostconfusingpartofusingafunction.Each
functionrequiresadifferentsetofargumentsandtheargumentsmustmatchcertain
criteriaorthefunctionwillreturnanerror(#ERR,#N/A,#DIV/0,etc.).Inthethirdoption
above,theAVERAGEfunctionrequiredsomenumber,listofnumbers,orrangeasan
argument.WeenteredtherangeA1:A5fortheargument.Weassumedthatrange
containedvalidnumberdata,ifanycellinthatrangeA1:A5containedsomethingbesides
anumber,thenourfunctionmayhaveresultedinanerror.

Youcanalsouseformulasandfunctionstogether.Forexample,thesecondoptionof

theprevioussectionistwofunctions(SUMandCOUNT)inaformula(SUM/COUNT).
Wecoulduseanyofthemathematicoperatorsontheresultofanyfunctionaslongasthe
resultofthatfunctionisanumber.Somefunctionsreturnnon-numericdatasothatmay
resultinanerror.

FunctionArguments

Afewmoredetailsweneedtodiscussbeforewegettothefunctions.Intheprevious

section,wediscussedhowafunctionbeginswithanequalssignthenakeyword,thenan
openparentheses,thenthearguments,andlastlytheclosingparentheses.Somefunctions
suchasTODAY()requiresnoarguments.OtherargumentssuchasSUM,COUNT,
AVERAGErequireoneargumentbutthenallowformanymorearguments.Whenyou
enterafunction,excelshowsyouwhichargumentyouarecurrentlyenteringwiththepop-
upboxthatappearsbelowtheformulabar.Forexample,fortheSUMfunction,thepop-
upboxdisplaysSUM(number1,[number2],…).Thenumber1meansitisrequiredbutthe
brackets[]aroundthe[number2]indicateitisoptional.Notethecommaseparatingthe
arguments;whenyouenterafunction,youwillneedtoseparatetheargumentsbya
commatoo.The(…)meansthattherecouldbemanymoreargumentsthatmatchthetype
ofthelastargument.Inthiscase,therecouldbemanymorenumbers([number3],
[number4],etc).

Somefunctionshaveanoptionalpairofarguments.Forexample,theSUMIFS,

COUNTIFS,andAVERAGEIFSfunctionsallhavethearguments[criteria_rangeX,
criteriaX]whereXisthenextnumberinseries.Sincethebracketisaroundthepairor
arguments,thepairisoptionalbutifyouwanttoincludetheargument,thenyoumust
includebothargumentsofthepair.

Mostargumentscouldbefulfilledbyeitheravalueoracellreference.Most

experiencedExcelusersputalloftheargumentsincellsandthenreferencethecells
withintheargumentsofthefunction.Theexampleworkbooksarebuiltthisway.With
thismethod,youcanchangethevalueinthereferencecellsandthefunctionwillthen
changeinsteadofhavingtogointotheformulabarandchangethefunctiondirectly.I
hopeusingthismethodwillhelpyouunderstandtherelationshipbetweentheinputsand
outputsofthefunctionasbothcomponentsarevisible.

ExampleWorkbook

Toreallyunderstandatopic,mostpeopleneedtovisualizeit.Therefore,allofthe

functionsdiscussedinthiseBookareincludedinaworkbookwithover500examples.
YoucanfindtheexampleworkbookandVisualAidprintoutsatthelinkbelow.

Tohelpvisualizetheinputs,somefunctionsthatrequirearangehavetherange

addresssavedinacell.Theninthefunction,I’veaddedtheINDIRECTfunctionto
converttherangeaddresssavedinthecelltotheactualrangeofthefunction.For
example,let’ssayB5containstherangeaddress“A2:A4”andA2throughA4containsall
numbers.IfIwastoenterthefunction=SUM(B5),itwouldreturn0becauseB5contains
thetextstring“A2:A4”.Inordertoconvertthetext“A2:A4”intoanaddress,weneedto
addtheINDIRECTfunction.Sothefunctionshouldthenbe=SUM(INDIRECT(B5))
whichisequivalentto=SUM(A2:A4)sinceB5contains“A2:A4”.Theexamplesareset
upthiswaysoyoucanseetheaddressoftherangesthatgointothefunctions.Tochange
theaddress,changeitinthecellwiththeaddress(inthemostrecentexample,thatcell
wouldbeB5).

Findtheexampleworkbookhere:

https://www.dropbox.com/sh/uvk1a6pdwjlrtjl/AAB7CXA0QlDBMQ9-Ps6iQ4Aka?dl=0

FunctionGuide

ThisFunctionGuideliststhekeywordthentheargumentsanddescriptionforeach

function.Wheneverthedescriptionreferencesthearguments,theargumentnamewillbe
inbold.Mostdescriptionswillbeginwiththeword“returns.”Theargumentsarethe
inputofthefunctionandthenthefunctionreturnstheoutput.Theoutputisvisibleinthe
cellwhilethefunctionisstillvisibleintheformulabarorbydoubleclickinginthecell.

Somefunctionsinthefunctionguidemustbeenteredasanarray.Toenterafunction

asanarray,holddownthecontrolandshiftbuttonswhilepressingenter(formacusers,
holddowncontrolandshiftwhilepressingreturn).Youwillknowthefunctionwas
enteredasanarraybythecurlybrackets{}aroundthefunctionintheformulabar.Donot
enterthecurlybracketsmanually.

DateFunctions

UsingDatesinExcel

Excelstoresdatesasnumberscalledserialnumbers.Zerocorrespondsto1/1/1904.

Thenumber100refersto100dayssince1/1/1904.Timesarestoredaspartialdays.So
12:00noonishalfthedaysoitisstoredas0.5addedtothedate.

AddingandSubtractingDatesandTimes

Toaddorsubtractadate,justaddorsubtractwholenumbers.Toaddorsubtracta

time,addorsubtractthepartialday.Ifyouwantedtoseewhattimeitwasafter5hours,it
iseasierandmoreexacttoadd5/24toadatethanitistorememberthat5/24=
.208333333.

DATE

Arguments:year,month,day

Description:Returnstheserialnumberofthedateofthespecifiedyear,month,andday.

DAY

Arguments:serial_number

Description:Returnsthedayofthemonth(1-31)oftheserial_number.

DAYS

Arguments:end_date,start_date

Description:Returnsthenumberofdaysbetweenend_dateandstart_date.Equivalentto
end_date-start_date.

EDATE

Arguments:start_date,months

Description:Returnstheserialnumberofthedatethatisthespecifiednumberofmonths
beforeorafterthestart_date.Ifmonthsislessthan0thenitreturnsmonthsbefore;if
greaterthan0thenmonthsafter.

EOMONTH

Arguments:start_date,months

Description:Returnstheserialnumberofthelastdayofthemonththatisthespecified
numberofmonthsbeforeorafterthestart_date.Ifmonthsislessthan0thenitreturns
monthsbefore;ifgreaterthan0thenmonthsafter.

HOUR

Arguments:serial_number

Description:Returnsthehour(0-23)oftheserial_number.

MINUTE

Arguments:serial_number

Description:Returnstheminute(0-59)oftheserial_number.

MONTH

Arguments:serial_number

Description:Returnsthemonth(1-12)oftheserial_number.

NETWORKDAYS

Arguments:start_date,end_date,[holidays]

Description:Returnsthenumberoffullworkdaysbetweenstart_dateandend_date.
Specifythedatesofanyholidaystoexclude.

NOW

Arguments:none

Description:Returnsthecomputer’sdateandtime.

SECOND

Arguments:serial_number

Description:Returnsthesecond(0-59)oftheserial_number.

TIME

Arguments:hour,minute,second

Description:Returnstheserialnumberofthetimeofthespecifiedhour,minute,and
second.

TODAY

Arguments:none

Description:Returnsthecomputer’sdate.

WEEKDAY

Arguments:serial_number,[return_type]

Description:Returnsanumber(1-7)ofthedayintheweekfortheserial_number.Use
return_typetodeterminehowtocountthedays.

WEEKNUM

Arguments:serial_number,[return_type]

Description:Returnstheweeknumberoftheyearfortheserial_number.Use
return_typetodeterminehowtocounttheweeks.

WORKDAY

Arguments:start_date,days,[holidays]

Description:Returnstheserialnumberofthenumberofdaysafterthestart_date.
Specifythedatesofanyholidaystoexclude.

YEAR

Arguments:serial_number

Description:Returnstheyearoftheserial_number.

FinancialFunctions

NotesforFinancialFunctions

Thereareafewthingsinmindwhenusingthefunctionsandtheirargumentsdescribed
below.Besuretokeepunitsconsistentforrateandnper.Ifmonthlypaymentsthenrate
willbeAPR/12andnperisnumberofyearsofloan*12.Forannualpayments,thenrate
willbeAPRandnperisnumberofyears.Cashdepositedintosavingsisrepresentedby
negativenumbersandcashreceivedbypositivenumbers.Iftypeis0oromitted,then
paymentsaremadeattheendoftheperiod.Iftypeis1thenpaymentsaremadeatthe
beginningoftheperiod.

FV

Arguments:rate,nper,pmt,[pv],[type]

Description:Returnsthefuturevalueofaninvestmentofpvatinterestrateratebasedon
thenumbernperofconstantpaymentspmt.

PV

Arguments:rate,nper,pmt,[fv],[type]

Description:Returnsthepresentvalueofaninvestmentwithtargetfuturevaluefvat
interestrateratebasedonthenumbernperofconstantpaymentspmt.

RATE

Arguments:nper,pmt,pv,[fv],[type],[guess]

Description:Returnstheinterestrateperperiodofaloan/investmentofsizepvwithnper
paymentsofconstantvaluepmt.Fvisthecashbalanceafterthelastpayment.Iffvis
omitted,0isassumed.Guessistheguessofwhattheinterestrateshouldbe.Ifguessis
omitted,itisassumedtobe10percent.

PMT

Arguments:rate,nper,pv,[fv],[type]

Description:Returnsthepaymentperperiodofaloan/investmentofsizepvwithnper
paymentswithaninterestraterate.Fvisthecashbalanceafterthelastpayment.Iffvis
omitted,0isassumed.

LogicalFunctions

AND

Arguments:logical1,logical2,…

Description:ReturnsTRUEifalllogicalargumentsareTRUE.

FALSE

Arguments:none

Description:ReturnsthevalueFALSE.

IF

Arguments:logical_test,value_if_true,value_if_false

Description:Iflogical_testreturnsTRUEthenreturnthevalue_if_true,otherwisereturn
thevalue_if_false.

IFERROR

Arguments:value,value_if_error

Description:Ifvalueisanerror(#N/A,#DIV/0,etc.)thenreturnthevalue_if_error,
otherwisereturnsvalue.

IFNA

Arguments:value,value_if_na

Description:Ifvalueis#N/Athenreturnsthevalue_if_na,otherwisereturnsvalue.

ISBLANK

Arguments:value

Description:IfvalueisblankthenreturnsTRUE,ifnotthenreturnsFALSE.

ISERR

Arguments:value

Description:Ifvalueisanerrorotherthan#N/AthenreturnsTRUE,ifnotthenreturns
FALSE.

ISERROR

Arguments:value

Description:Ifvalueisanerror(#N/A,#DIV/0,etc.)thenreturnsTRUE,ifvalueisnot
anerrorthenreturnsFALSE.

ISNUMBER

Arguments:value

Description:IfvalueisanumberthenreturnsTRUE,ifnotthenreturnsFALSE.

NOT

Arguments:logical

Description:IflogicalwouldreturnTRUEthenreturnFALSEorifFALSEthenreturn
TRUE.

OR

Arguments:logical1,logical2,…

Description:ReturnsTRUEifatleastonelogicalargumentisTRUE.

TRUE

Arguments:none

Description:ReturnsthevalueTRUE.

LookupFunctions

ADDRESS

Arguments:row_num,column_num,[abs_num],[a1],[sheet_text]

Description:Returnsacellreferenceastextfromcolumn_numandrow_num.Use
abs_numtoidentifyabsoluteorrelativereferencesanda1forA1orR1C1styleand
sheet_texttospecifythesheetname.

CHOOSE

Arguments:index_num,value1,[value2],…

Description:Returnsavalue(value1,value2,…)basedonachoiceforindex_num.Like
anifstatementwithmoreoptions.Index_numcannotbegreaterthanthenumberof
values.

COLUMN

Arguments:[reference]

Description:Returnsanumberofthecolumnofthereference.Ifreferenceisleftblank
thenreturnsthecolumnofthecellcontainingtheformula.

COLUMNS

Arguments:array

Description:Returnsthenumberofcolumnsintherangearray.

FORMULATEXT

Arguments:reference

Description:Returnstheformulathatisstoredinreferenceasatextstring.

GETPIVOTDATA

Arguments:data_field,pivot_table,[Field1,item1],[Field2,item2],…

Description:Returnsavaluefromthedata_fieldinthepivottablepivot_tablewhich
meetsallofthecriteriaofiteminField.

HLOOKUP

Arguments:lookup_value,table_array,row_index_num,[range_lookup]

Description:Findslookup_valueinthetoprowoftherangetable_arrayandthenreturns
thevaluefromthesamecolumnintherow_index_numrowofthattable.Forexample,if
youwanttoreturnthethirdrowthenrow_index_num=3.Ifrange_lookupisFALSE,
thenthevalueinthetoprowmustmatchlookup_valueexactly.Ifrange_lookupis
TRUE,thenitwillmatchtheclosestvaluetolookup_valuewithoutgoingover.

HYPERLINK

Arguments:link_location,[friendly_name]

Description:Returnsthetextoffriendly_nameasahyperlinktolink_location.If
friendly_nameisleftblank,thenthetextoflink_locationwillbereturnedasahyperlink.

INDEX(array)

Arguments:array,row_num,[column_num]

Description:Returnsthecellreferenceattheintersectionofrow_numandcolumn_num
withinarray.Enterasanarrayfunctionwitheitherrow_numorcolumn_numas0
(zero)toreturntheentireroworcolumn.

INDEX(reference)

Arguments:reference,row_num,[column_num],[area_num]

Description:Returnsthecellreferenceattheintersectionofrow_numandcolumn_num
withinreference.Ifreferencecontainsmultipleranges,thenarea_numcanbeusedto
identifywhichrange.Ifrow_numorcolumn_numis0(zero)thenreturnstheentirerow
orcolumn.

INDIRECT

Arguments:ref_text,[a1]

Description:Returnsthereferencespecifiedbythetextstringref_text.Ifa1isTRUEor
omitted,thenref_textisinterpretedasanA1-stylereference.Ifnot,thenitisinterpreted
asanR1C1-stylereference.

MATCH

Arguments:lookup_value,lookup_array,[match_type]

Description:Looksforlookup_valuewithinalookup_arrayofcellsandreturnsthe
positionofthatitemintherange.Ifmatch_typeis1oromitted,thenreturnsthelargest
valuethatislessthanorequaltolookup_valueandvaluesinlookup_arraymustbe
sortedinascendingorder.Ifmatch_typeis0,thenreturnsthefirstvaluethatisexactly
equaltolookup_valueandvaluescanbesortedinanyorder.Ifmatch_typeis-1,then
returnsthesmallestvaluethatisgreaterthanorequaltolookup_valueandvaluesin
lookup_arraymustbesortedindescendingorder.

OFFSET

Arguments:reference,rows,cols,[height],[width]

Description:Returnsthereferencetotherangethatisrowsrowsandcolscolumnsaway
fromreference.Useheightandwidthtospecifythenumberofrowsandcolumns
returned.

ROW

Arguments:[reference]

Description:Returnsanumberoftherowofthereference.Ifreferenceisleftblankthen
returnstherowoftheformula.

ROWS

Arguments:array

Description:Returnsthenumberofrowsintherangearray.

VLOOKUP

Arguments:lookup_value,table_array,col_index_num,[range_lookup]

Description:Findslookup_valueinthefirstcolumnoftherangetable_arrayandthen
returnsthevaluefromthesamerowinthecol_index_numcolumnofthattable.For
example,ifyouwanttoreturnthethirdcolumnthencol_index_num=3.If
range_lookupisFALSE,thenthevalueinthefirstcolumnmustmatchlookup_value
exactly.Ifrange_lookupisTRUE,thenitwillmatchtheclosestvaluetolookup_value
withoutgoingoverandtable_arraymustbesortedinascendingorder.

MathFunctions

ABS

Arguments:number

Description:Returnstheabsolutevalueofnumber.

CONVERT

Arguments:number,from_unit,to_unit

Description:Returnstheresultofconvertingnumberinfrom_unittotheequivalent
valueinto_unit.

DELTA

Arguments:number1,[number2]

Description:ReturnsTRUE(1)orFALSE(0)ifnumber1isequaltonumber2.

EXP

Arguments:number

Description:Returnsthevalueofe(Euler’snumber)raisedtothepowernumber.

FACT

Arguments:number

Description:Returnsthevalueofnumber‘sfactorial.

INT

Arguments:number

Description:Returnsnumberroundeddownthenearestinteger.

LOG

Arguments:number,[base]

Description:Returnsthelogarithmwithbasebaseofnumber.

LOG10

Arguments:number

Description:Returnsthecommonlogarithm(base10)ofnumber.

LN

Arguments:number

Description:Returnsthenaturallogarithmofnumber.

MOD

Arguments:number,divisor

Description:Returnstheremainderofnumberdividedbydivisor.

MROUND

Arguments:number,multiple

Description:Returnsnumberroundedtothenearestmultiple.

PI

Arguments:none

Description:ReturnsthevalueofPi.

POWER

Arguments:number,power

Description:Returnsnumberraisedtotheexponentpower.

PRODUCT

Arguments:number1,[number2],…

Description:Returnsthevalueofallnumberargumentsmultipliedtogether.

RAND

Arguments:none

Description:Returnsarandomnumberbetween0and1.

RANDBETWEEN

Arguments:bottom,top

Description:Returnsarandomnumberbetweenbottomandtop.

ROUND

Arguments:number,num_digits

Description:Returnsnumberroundedtothedecimalplacespecifiedbynum_digits.If
num_digitsispositive,thenplacestotherightofthedecimal.Ifnegative,thenplacesto
theleftofthedecimal.

ROUNDDOWN

Arguments:number,num_digits

Description:Returnsnumberroundeddowntothedecimalplacespecifiedby
num_digits.Ifnum_digitsispositive,thenplacestotherightofthedecimal.If
negative,thenplacestotheleftofthedecimal.

ROUNDUP

Arguments:number,num_digits

Description:Returnsnumberroundeduptothedecimalplacespecifiedbynum_digits.
Ifnum_digitsispositive,thenplacestotherightofthedecimal.Ifnegative,thenplaces
totheleftofthedecimal.

SIGN

Arguments:number

Description:Returns1ifnumberispositive,-1ifnegative,or0if0.

SQRT

Arguments:number

Description:Returnsthesquarerootofnumber.

SUBTOTAL

Arguments:function_num,ref1,[ref2],…

Description:Returnsthesubtotalofalistordatabasewithref1,ref2,etc.Thevalueofthe
subtotalcanchangebasedonthefunction_num.

SUM

Arguments:number1,[number2],…

Description:Returnsthevalueofallnumberargumentsaddedtogether.

SUMIF

Arguments:range,criteria,[sum_range]

Description:Returnsthevalueofsum_rangeaddedtogetherifthecorrespondingvaluein
rangemeetsthecriteria.

SUMIFS

Arguments:sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…

Description:Returnsthevalueofsum_rangeaddedtogetherifallofthecorresponding
valuesinallcriteria_rangemeetsallcriteria.

SUMPRODUCT

Arguments:array1,[array2],…

Description:Returnsthesumofcorrespondingvaluesinarraysmultipliedtogether.

SUMSQ

Arguments:number1,[number2],…

Description:Returnsthesumofthenumberargumentssquared.Numberargumentsare
squaredfirstandthenaddedtogether.

TRUNC

Arguments:number,[num_digits]

Description:Returnsnumbertruncatedorcutofftothenumberofdecimalplacesin
num_digits.Ifnum_digitsisleftblank,thennumberistruncatedtoaninteger.
Truncationdoesnotinvolverounding.

StatisticalFunctions

AVERAGE

Arguments:number1,[number2],…

Description:Returnsthearithmeticmeanofthenumberarguments.

AVERAGEIF

Arguments:range,criteria,[average_range]

Description:Returnsthearithmeticmeanoftheaverage_rangeifthecorrespondingvalue
inrangemeetsthecriteria.

AVERAGEIFS

Arguments:average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…

Description:Returnsthearithmeticmeanoftheaverage_rangeifallofthecorresponding
valuesincriteria_rangemeetthecriteria.

CORREL

Arguments:array1,array2

Description:Returnsthecorrelationcoefficientbetweenthetwodatasetsarray1and
array2.

COUNT

Arguments:value1,[value2],…

Description:Returnsthenumberofcellsinarange(value1,value2,…)withnumbers.

COUNTA

Arguments:value1,[value2],…

Description:Returnsthenumberofcellsinarange(value1,value2,…)thatarenot
empty.

COUNTBLANK

Arguments:range

Description:Returnsthenumberofcellsinrangethatareempty.

COUNTIF

Arguments:range,criteria

Description:Returnsthenumberofcellsinarangethatmeetthespecifiedcriteria.

COUNTIFS

Arguments:criteria_range1,criteria1,[criteria_range2,criteria2],…

Description:Returnsthenumberofcellsthatmeetallofthespecifiedcriteriaintheir
respectivecriteria_ranges.

FORECAST

Arguments:x,known_y’s,known_x’s

Description:Returnsafuturevalueatpointxalongalineartrendusingexistingdata
(known_y’sandknown_x’s).

F.TEST

Arguments:array1,array2

Description:ReturnstheresultoftheF-Testwhichmeasurestheprobabilitythatthe
variancesoftwoarraysarenotsignificantlydifferent.

INTERCEPT

Arguments:known_y’s,known_x’s

Description:Returnsthepointatwhichalinewillintersectthey-axisusinglinear
regressionofknown_y’sandknown_x’s.

LARGE

Arguments:array,k

Description:Returnsthek-thlargestvalueintherangearray.

MAX

Arguments:number1,[number2],…

Description:Returnsthelargestvalueinarange(number1,number2,…).

MEDIAN

Arguments:number1,[number2],…

Description:Returnsthemedianofthevaluesinarange(number1,number2,…).

MIN

Arguments:number1,[number2],…

Description:Returnsthesmallestvalueinarange(number1,number2,…).

MODE.MULT

Arguments:number1,[number2],…

Description:Returnsaverticalarrayofthemostcommonlyoccurringvaluesinthe
number1,number2,…Highlightthenumberofcellsinacolumntoreturn,thenpress
control-shift-entersimultaneouslytoenterasanarrayfunction.

MODE.SNGL

Arguments:number1,[number2],…

Description:Returnsthemostcommonvalueinthenumber1,number2,…array.

PEARSON

Arguments:known_y’s,known_x’s

Description:ReturnsthePearsonProductMomentCoefficientofknown_y’sand
known_x’s.ThePearsonProductMomentCoefficientisabbreviatedR.

PERCENTILE.INC

Arguments:array,k

Description:Returnsthevaluelocatedatthekthpercentileofanarray.
PERCENTILE.EXCisslightlymoreaccuratethanPERCENTILE.INCbutitwillonly
workifkisbetween1/nand1-1/nwherenisthenumberofelementsinarray.

PERCENTILE.EXC

Arguments:array,k

Description:Returnsthevaluelocatedatthekthpercentileofanarray.
PERCENTILE.INCisslightlylessaccuratethanPERCENTILE.EXCbutitwillworkfor
avalueofkbetween0and1.

PERCENTRANK.INC

Arguments:array,x,[significance]

Description:Returnsthepercentagerankofavaluexinanarray.Inclusiveofthedata
set.

PERCENTRANK.EXC

Arguments:array,x,[significance]

Description:Returnsthepercentagerankofavaluexinanarray.Exclusiveofthedata
set.

QUARTILE.EXC

Arguments:array,quart

Description:Returnstheexclusivequart(0-4)quartileoftherangearray.

QUARTILE.INC

Arguments:array,quart

Description:Returnstheinclusivequart(0-4)quartileoftherangearray.

RANK.AVG

Arguments:number,ref,[order]

Description:Returnstherankofanumberinrangerefsortedascendingordescending
basedonorder.Ifmultipleranksarefound,theaveragerankisreturned.Iforderisleft
blank,theorderwillbeascending.

RANK.EQ

Arguments:number,ref,[order]

Description:Returnstherankofanumberinrangerefsortedascendingordescending
basedonorder.Ifmultipleranksarefound,thetoprankisreturned.Iforderisleft
blank,theorderwillbeascending.

RSQ

Arguments:known_y’s,known_x’s

Description:ReturnsthesquareofthePearsonProductMomentCoefficientofknown_y’s
andknown_x’s.KnownasR-squared.

SLOPE

Arguments:known_y’s,known_x’s

Description:Returnstheslopeofalineusinglinearregressionofknown_y’sand
known_x’s.

SMALL

Arguments:array,k

Description:Returnsthek-thsmallestvalueintherangearray.

TRIMMEAN

Arguments:array,percent

Description:Returnsthearithmeticmeanoftheinteriorpercentofrangearray.

T.TEST

Arguments:array1,array2,tails,type

Description:ReturnstheprobabilityofaStudent’sT-Testcomparingtheaverageof
array1andarray2withnumberoftailsandtype.

Z.TEST

Arguments:array,x,[sigma]

Description:Returnstheone-tailedvalueofaZ-Testofarraywithvaluexandsigma
levelsigma.

TextFunctions

Whatisastring?

Incomputerlingo,aseriesoftextcharactersiscalledastring.Astringcanbecomposed
ofletters,numbers,orsymbols.

CLEAN

Arguments:text

Description:Returnstextwithallnon-printablecharactersremoved.

CONCATENATE

Arguments:text1,text2,etc.

Description:Returnsthecombinationofmultipletextstrings.

EXACT

Arguments:text1,text2

Description:ReturnsTRUEiftwotextstringsareexactlythesame(including
upper/lowercase).

FIND

Arguments:find_text,within_text,start_num

Description:Findsonestring(find_text)withinanotherstring(within_text).Canspecify
wheretostartlookingwithstart_num.

LEFT

Arguments:text,num_chars

Description:Returnstheleftmostnum_charsofthetextstring.

LOWER

Arguments:text

Description:Returnstextwithalllettersinlowercase.

MID

Arguments:text,start_num,num_chars

Description:Returnsthenum_charsofthetextstringstartingatstart_num.

PROPER

Arguments:text

Description:Returnstextwiththefirstletterineachwordinuppercaseandtherestto
lowercase.

REPLACE

Arguments:old_text,start_num,num_chars,new_text

Description:Replacesnum_charscharactersstartingatstart_numofold_textwith
new_text.

RIGHT

Arguments:text,num_chars

Description:Returnstherightmostnum_charsofthetextstring.

SEARCH

Arguments:find_text,within_text,start_num

Description:Findsonestring(find_text)withinanotherstring(within_text).Canspecify
wheretostartlookingwithstart_num.SEARCHismoreversatilethanFIND.FINDis
casesensitivewhereSEARCHisnot.SEARCHalsoacceptsthewildcardcharacters?for
singleand*formultiplecharacters.

TEXT

Arguments:value,format_text

Description:Returnsvaluereformattedaccordingtotheformat_textspecified.

TRIM

Arguments:text

Description:Returnstextwithallspacesremovedexceptforasinglespacebetween
words.

UPPER

Arguments:text

Description:Returnstextwithalllettersinuppercase.

AbouttheAuthor

ScottRatliffholdsaBachelor’sofSciencedegreeinMaterialsScienceand

EngineeringfromtheUniversityofKentucky.HeisalsoacertifiedMicrosoftExcel2013
Expert.HeisworkingtowardshisSixSigmaBlackBeltaccreditationwithafocuson
LeanManufacturingImprovements.HelivesinKentuckywithhiswifeandtwo
daughters.FindhisentirelistofLeanExcelBooksonhiswebsite

www.LeanExcelBooks.com

.

OtherBooksbyScottRatliff

LeanExcel:DynamicCharts

Eliminateunnecessarystepsanddrawdata-basedconclusionsfasterbycreatingdynamic
chartsinMicrosoftExcel!Chartsthatupdateautomaticallywhenyouaddordeletedata.
ThiseBookcontainsadetailedprocedureonhowtocreatedynamicchartsaswellasafull
explanationofeachstepforyourlearning.Alsoincludedisadownloadabletemplatewith
threetypesofdynamicchartsinplacesoyoucanseethefinalresultaswellasaprintable
one-pagevisualinstructionguidetokeepatyourdesktohaveanytimeyouneedit!
Increaseyourproductivityimmediately!RequiresMicrosoftExcel97ornewer.
Availableonboth

AmazonKindle

and

iBooks

.

Alleluia:FamilyWorshipNotebook

UsetheAlleluia:FamilyWorshipNotebooktohelpyouandyourfamilygrowcloserto
GodandmoreknowledgeableaboutHisWord!Theincludedtoolsprovideastructured
formatyouandyourfamilycanusetorecord,review,andretaintheworshipservicesof
yourlocalchurch.Differentlevelnotebookpagesareincludedforgrade1throughadults.
FamilyBiblestudyquestionsareincludedtohelpstartorrestartyourfamilyBiblestudies.
Availableonboth

AmazonKindle

and

iBooks

.