Ms excel 2016_function

Written By: Aziz Azizi
Language: English

1. Intro To Microsoft Excel 2016 Function
Aziz M Azizi
AsmanInstitute
ICTDepartment

2. Formula, Function
Microsoft Excel 2016 Functions
Formula:
Aformulaisasequenceofvalues,cellreferences,
names,functions,oroperatorsinacellthattogether
produceanewvalue.Formulasareequationsthat
performcalculationsonvaluesinyourworksheet.A
formulaalwaysstartswithanequalsign(=).
Youcancreateasimpleformulabyusingconstants
andcalculationoperators.
Function:
Afunctionisaprewrittenformulathattakesavalue
orvalues,performsanoperation,andreturnsavalue
orvalues.Usefunctionstosimplifyandshorten
formulasonaworksheet,especiallythosethat
performlengthyorcomplexcalculations.Afunction
startswithanequalsign(=).

3. Terms
Microsoft Excel 2016 Functions
Cellreferences:
Youcanrefertodatainworksheetcellsbyincluding
cellreferencesintheformula.Forexample,thecell
referenceA2returnsthevalueofthatcellorusesthat
valueinthecalculation.
Constants:
Constantsareentriesthathaveaspecificfixed
value.
Range:
Arangeisagrouporblockofcellsinaworksheet
thathavebeenselectedorhighlighted(A1:A10)

4. Contd…
Microsoft Excel 2016 Functions
Arguments:
MostExcelfunctionsaredesignedtoacceptdatathatit
thenusesinitscalculations.Thisdataisreferredtoas
argumentsandgobetweentheroundbracketsthatfollow
thefunctionsname.theIFfunction,forexample,hasthree
distinctarguments:Thelogictest,thevalueiftrue,thevalue
iffalseandthe=IF(logictest,valueiftrue,valueiffalse)
Array:
Anarrayisrangeorgroupofrelateddatavalues.Ina
spreadsheetprogramsuchasExcelthevaluesinanarray
arenormallystoredinadjacentcells.
TherearetwotypesofarrayinExcel:
One-dimensionalarray:Wherethedataislocatedinsinglecolumnor
row.
Two-dimensionalarray:wherethedataislocatedinmultiplecolumns
orrows.

5. Operators:
Microsoft Excel 2016 Functions
Operators:
Operatorsarethesymbolsthatareusedtospecifythe
typeofcalculationthatyouwanttheformulatoperform.
Forexample,the^(caret)operatorraisesanumbertoa
power,andthe*(asterisk)operatormultipliesnumbers.
Typesofoperators:
Therearefourdifferenttypesofcalculationoperators:
arithmetic,comparison,textconcatenation,andreference.
Arithmeticoperators:
Toperformbasicmathematicaloperationssuchas
addition,subtraction,ormultiplication;combinenumbers;
andproducenumericresults,usethefollowingarithmetic
operators.

6. Contd…
Microsoft Excel 2016 Functions
Arithmetic OperatorMeaning Example
+ (Plus Sign) Addition 2+2
-(Minus Sign) Subtraction-Negation3-2
* (Asterisk) Multiplication 2*4
/ (Forward Slash) Division 6/2
% (PercentSign) Percent 10%
^ (Caret( Exponentiation 4^2

7. Contd…
Microsoft Excel 2016 Functions
Comparisonoperators:
Youcancomparetwovalueswiththefollowingoperators.
Whentwovaluesarecomparedbyusingtheseoperators,
theresultisalogicalvalueeitherTRUEorFALSE.
Arithmetic Operator Meaning Example
=(Equal Sign) EqualTo 2=2
>(Greater Than Sign) GreaterThan 3>2
<(Less Than Sign) LessThan 2<4
>=(Greater Than or Equal To Sign)GreaterThan or Eqlto6>=2
<=(Less Than or Equal To Sign)LessThan or Eqlto 2<=6
<>Not Equal To Sign NotEqual To 4<>2

8. Contd…
Microsoft Excel 2016 Functions
Text Operator Meaning Example
&(Ampersand)
Connects, or
concatenates, two
values to produce on
continuous text value.
(A1 & A2)
TextConcatenationoperator:
Usetheampersand(&)tojoin,orconcatenate,oneor
moretextstringstoproduceasinglepieceoftext.

9. Contd…
Microsoft Excel 2016 Functions
Reference operators:
Combine ranges of cells for calculations with the following
operators.
Reference OperatorMeaning Example
: (Colon) Rangeoperator, which
produces one reference to all
the cells between two
references, including the two
references.
(A1:A10)
, (Comma) Union operator, which
combines multiple references
to one reference.
(A1:A10,B1:B10)

10. Contd…
Microsoft Excel 2016 Functions
References:
Whenyouuseacell(orrange)referenceinaformula,you
canusethreetypesofreferences:
Relative:Therowandcolumnreferencescanchange
whenyoucopytheformulatoanothercellbecausethe
referencesareactuallyoffsetsfromthecurrentrowand
column.Bydefault,Excelcreatesrelativecellreferencesin
formulas.
Absolute:Therowandcolumnreferencesdonotchange
whenyoucopytheformulabecausethereferenceistoan
actualcelladdress.Anabsolutereferenceusestwodollar
signsinitsaddress;oneforthecolumnletterandonefor
therownumber(Forexample,$A$5).
Mixed:Boththeroworcolumnreferenceisrelative,and
theotherisabsolute.Onlyoneoftheaddresspartsis
absolute(Forexample,$A4orA$4).

11. Four Functions
Microsoft Excel 2016 Functions
Addition:
ThesimplestExceladditionformulacalculatesthe
sumofnumbersandreturnstheresult.

12. Contd…
Microsoft Excel 2016 Functions
Insteadofusingthe+operator,asshownabove,you
canusetheSumfunctioninyourExceladdition
formula.

13. Contd…
Microsoft Excel 2016 Functions
Subtraction:
ThesimplestExcelsubtractionformulasubtractsthe
numbersandreturnstheresult.

14. Contd…
Microsoft Excel 2016 Functions
Multiplication:Thebasicformulaforperforming
multiplicationinExcel,calculatesproductofthenumbers
andreturnstheresult.
Insteadofusingthe*operator,asshownabove,youcanuse
theProductfunctiontoperformamultiplicationinExcel.

15. Contd…
Microsoft Excel 2016 Functions
Division:
ThemostbasicformulatodivideinExceldivision,
dividesthenumberandreturnstheresult.

16. Displaying Percentage
Microsoft Excel 2016 Functions
PercentagesinExcelarestoredasdecimalvalues.For
example,25%isstoredasthevalue0.25,50%isstoredas
thevalue0.5,etc.
Itisonlytheformattingofacellthatmakesthedecimal
valueappearasapercentage.
TwoPercentageCalculationTypes
WhencalculatingpercentagesinExcel,therearetwotypes
thataretypicallyused.Theseare:
PercentageasaProportion:Example:if,outof120
questions,youanswer90correctly,howisthisexpressed
asapercentagescore?

17. Contd…
Microsoft Excel 2016 Functions
PercentageChange:Example;ifyoursalesfiguresare
$1,250inJanuaryand$1,750inFebruary,whatisthe
percentageincreaseinFebruary,comparedtoJanuary?
1.HowtoCalculateaPercentageasaProportion
Ifyouwanttocalculateasampleofafullset,asa
percentage,thisisdonebydividingthesamplesizeby
thesizeofthefullset.
Forexample,ifyoutakeanexamandanswer90outof
120questionscorrectly,thiscanbeexpressedasa
percentagebysimplydividing90by120.

18. Contd…
Microsoft Excel 2016 Functions
Thisgivestheresult0.75,or75%
2.HowtoCalculatePercentageChange
Ifyouwanttocalculatethepercentagechangebetween
valueAandValueB,thisisdoneusingthefollowing
formula.

19. Contd…
Microsoft Excel 2016 Functions
Percentagechange=(b-a)/a
Forexampleifyoursalesfiguresare$1,250in
Januaryand$1,750inFebruary,thepercentage
changeinFebruary,comparedtoJanuary,isgiven
by:

20. Statistic Functions
Microsoft Excel 2016 Functions
MAX:TheExcelMAXfunctionreturnsthelargestvalue
fromasuppliedsetofnumericalvalues.
thesyntaxofthefunctionis:Max(Number1,[number2],…)

21. Contd…
Microsoft Excel 2016 Functions
MAXA:TheExcelMAXAFunctionreturnsthelargest
valuefromasuppliedsetofnumericalvalues,while
countingtextandthelogicalvalueFALSEasthevalue0
andcountingthelogicalvalueTRUEasthevalue1.
Thesyntaxofthefunctionis:MAXA(number1,[number2],…)

22. Contd…
Microsoft Excel 2016 Functions
MIN:TheExcelMINfunctionreturnsthesmallestvalue
fromasuppliedsetofnumericalvalues.
Thesyntaxofthefunctionis:MIN(Number1,[number2],…)

23. Contd…
Microsoft Excel 2016 Functions
MINA:TheExcelMINAfunctionreturnsthesmallest
valuefromasuppliedsetofnumericalvalues,while
countingtextandthelogicalvalueFALSEasthevalue0
andcountingthelogicalvalueTrueasthevalue1.

24. Contd…
Microsoft Excel 2016 Functions
Large:TheExcelLARGEfunctionreturnsthek’th
largestvaluefromanarrayorarangeofcells
containingnumericalvalues.Theformatofthe
functionis:LARGE(array,k)
array-Anarrayofnumericvaluesthatyouwantto
returnthek’thlargestof
k-Theindexthefunctionreturnsthek’thlargestvalue
fromthesuppliedarray.
Examples
ThefollowingexampleshowstheExcelLargefunction,
beingusedtoretrievethe1
st
,2
nd
,3
rd
,4
th
and5
th
largest
valuesfromthesetofvaluesincellsA1-A5.

25. Contd…
Microsoft Excel 2016 Functions

26. Contd…
Microsoft Excel 2016 Functions
Small:TheExcelSMALLfunctionreturnsthek’th
smallestvaluefromanarrayorarangeofcells
containingnumericalvalues.
Theformatofthefunctionis:SMALL(array,k)
array-Anarrayofnumericvaluesthatyouwanttoreturn
thek’thsmallestof
k-Theindexthefunctionreturnsthek’thsmallestvalue
fromthesuppliedarray.
Examples:ThefollowingexampleshowstheExcelSmall
function,beingusedtoretrievethe1
st
,2
nd
,3
rd
,4
th
,and5
th
smallestvaluesfromthesetofvaluesincellsA1-A5.

27. Contd…
Microsoft Excel 2016 Functions

28. Contd…
Microsoft Excel 2016 Functions
Average:TheExcelAVERAGE functionreturnsthe
arithmeticmeanofalistofsuppliednumbers.
Thesyntaxofthefunctionis:Average(number1,[number2],
…)

29. Contd…
Microsoft Excel 2016 Functions
Avergea:
TheExcelAVERAGEAfunctionreturnsthe
arithmeticmeanofalistofsuppliednumbers.
The syntax of the function is:
AVERAGEA(value1, [value2], …)

30. Contd…
Microsoft Excel 2016 Functions
AVERAGE
Function
AVERAGEA
Function
Logical values or text
representationsofnumber,typed
directlyintothelistofarguments.
Are counted
(TRUE=1,
FALSE=0)
Arecounted
(TRUE=1, FALSE=0)
Textthatcannotbeinterpretedasa
number,typeddirectlyintothelist
ofarguments.
#VALUE!error#VALUE!Error
Logicalvalues,withinarraysor
referencearguments
Ignored Are counted
(TRUE-1, FALSE=0)
Text(includingemptytext“”,text
representationsofnumbers,or
othertext),withinarraysor
referencearguments.
Ignored Countedas zero
Empty cells. Ignored Ignored

31. Contd…
Microsoft Excel 2016 Functions
Excel Average vs. Excel Averagea
TheExcelAverageFunctionisverysimilartotheExcel
AverageFunction.Theonlydifferencebetweenthesetwo
functionsisthewayinwhichlogicalvaluesortextvalues
withinarraysorreferencesaretreatedinthecalculationof
thearithmeticmean.Thisisshowinthetableonprevious
slide

32. Contd…
Microsoft Excel 2016 Functions
Averageif:
TheExcelAVERAGEIFfunctionfindsvaluesinasupplied
array,thatsatisfyagivencriteria,andreturnstheaverage
(thestatisticalmean)ofthecorrespondingvaluesina
secondsuppliedarray.
ThesyntaxoftheAverageiffunctionis:
AERAGEIF (range, criteria, [average_range])

33. Contd…
Microsoft Excel 2016 Functions

34. Contd…
Microsoft Excel 2016 Functions
Count:TheExcelCountfunctionreturnsthenumberof
numericvaluesinasuppliedsetofcellsorvalues.This
countincludesbothnumbersanddates.
The syntax of the function is:
COUNT(value1, [value2], …)

35. Contd…
Microsoft Excel 2016 Functions
Counta:
TheExcelCountafunctionreturnsthenumberofnon-
blanksinasuppliedsetofcellsand/orvalues.
Thesyntaxofthefunctionis:
COUNTA (value1, [value2], …)

36. Contd…
Microsoft Excel 2016 Functions
Countif:
TheExcelCountiffunctionreturnsthenumberofcells(of
asuppliedrange),thatsatisfyagivencriteria.
The syntax of the function is:
COUNTIF (range; criteria)

37. Contd…
Microsoft Excel 2016 Functions

38. Contd…
Microsoft Excel 2016 Functions
Countifs:
TheExcelCountifsfunctionreturnsthenumberofentries
(withinoneormoresuppliedarrays),thatsatisfyasetof
givencriteria.
ThefunctionisnewinExcel2007,andsoisnotavailablein
earlierversionsofExcel.
The syntax of the Countifsfunction is:
COUNTIFS (criteria_range1, criteria, [criteria_range2, criteria2],
…)

39. Contd…
Microsoft Excel 2016 Functions

40. Date & Time Functions
Microsoft Excel 2016 Functions
Date:TheExcelDATEfunction,representingayear,
monthandday,returnsanExceldate.
The syntax of the Date function is:
DATE (year, month, day)

41. Contd…
Microsoft Excel 2016 Functions
Datevalue:TheExcelDatevaluefunctionconvertsatext
representationofadate,intoanExceldate.
The syntax of the Datevaluefunction is:
DATEVALUE (date_text)

42. Contd…
Microsoft Excel 2016 Functions
Time:TheExcelTimefunctionacceptsthreeinteger
argumentsrepresentinghours,minutesandseconds,and
returnsanExceltime.
Thesyntaxofthefunctionis:
TIME (hour, minute, second)

43. Contd…
Microsoft Excel 2016 Functions
Timevalue:TheExcelTimevaluefunctionconvertsa
textrepresentationofatime,intoanExceltime.
ThesyntaxoftheTimevaluefunctionis:
TIMEVALUE (time_text)

44. Contd…
Microsoft Excel 2016 Functions
Now:TheExcelNowfunctionreturnsthecurrentdate
andtime.Thefunctionhasnoargumentsandtherefore,
thesyntaxofthefunctionis:NOW()
Today:TheExcelTodayfunctionreturnsthecurrent
date.Thefunctionhasnoargumentsandtherefore,the
syntaxofthefunctionissimply:TODAY()

45. Contd…
Microsoft Excel 2016 Functions
Hour:TheExcelHOURfunctionreturnsaninteger
representingthehourcomponentofasuppliedExcel
time.
ThesyntaxoftheHOURfunctionis:
HOUR (serial_number)

46. Contd…
Microsoft Excel 2016 Functions
Minute:TheExcelMinutefunctionreturnsaninteger
representingtheminutescomponentofasuppliedExcel
time.
ThesyntaxoftheMinutefunctionis:
MINUTE (serial_number)

47. Contd…
Microsoft Excel 2016 Functions
Second:TheExcelSecondfunctionreturnsaninteger
representingthesecondscomponentofasuppliedExcel
time.
The syntax of the Second function is:
SECOND (serial_number)

48. Contd…
Microsoft Excel 2016 Functions
Day:
TheExcelDAYfunction,whensuppliedwithadate,returns
anintegerrepresentingthedayofthemonth(from1-31).
Theformatofthefunctionis:
DAY (date)

49. Contd…
Microsoft Excel 2016 Functions
Month:
TheExcelMONTHfunction,whensuppliedwithadate,
returnsanintegerrepresentingthemonthoftheyear(from
1-12).Theformatofthefunctionis:
MONTH (date)

50. Contd…
Microsoft Excel 2016 Functions
Year:TheExcelYEARfunction,whensuppliedwitha
date,returnsanintegerrepresentingtheyear.
Theformatofthefunctionis:
YEAR (date)

51. Contd…
Microsoft Excel 2016 Functions
Weekday:TheExcelWeekdayfunctionreturnsan
integerrepresentingthedayoftheweekthatasupplied
datefallson.
Thesyntaxofthefunctionis:
WEEKDAY ( serial_number, [return_type] )

52. Contd…
Microsoft Excel 2016 Functions
Weeknum:TheExcelWEEKNUM functionreturnsan
integerrepresentingtheweeknumber(from1to53)of
theyear.
Thesyntaxofthefunctionis:
WEEKNUM (serial_number, [return_type] )

53. Contd…
Microsoft Excel 2016 Functions
Edate:TheExcelEdatefunctionreturnsadatethatisa
specifiednumberofmonthsbeforeorafterasupplied
startdate.Thesyntaxofthefunctionis:
EDATE (start_date, months)

54. Contd…
Microsoft Excel 2016 Functions
Eomonth:TheExcelEomonthfunctionreturnsthelast
dayofthemonth,thatisaspecifiednumberofmonths
beforeorafteraninitialsuppliedstartdate.Thesyntaxof
thefunctionis:
EMONTH (start_date, month)

55. Contd…
Microsoft Excel 2016 Functions
Days360:TheExcelDays360functionreturnsthe
numberofdaysbetween2dates,basedona360-dayyear
(12x30months).Theformatofthefunctionis:
DAYS360 (start_date, End_date, [Method] )

56. Math Functions
Microsoft Excel 2016 Functions
GCD: The Excel GCD function returns the greatest
common divisor of two or more supplied integers.
The format of the function is:
GCD (number1, number2, …)

57. Contd…
Microsoft Excel 2016 Functions
LCM: The Excel LCM function returns the least
common multiple of two or more supplied integers.
The format of the function is:
LCM (number1, number2, …)

58. Contd…
Microsoft Excel 2016 Functions
Product:TheExcelPRODUCT functionreturnsthe
product(multiplication)ofasuppliedsetofnumerical
values.
The syntax of the function is:
PRODUCT (number1, [number2], …)

59. Contd…
Microsoft Excel 2016 Functions
Power: The Excel power function calculates a given
number, raised to a supplied power.
The syntax of the function is:
POWER (number, power)

60. Contd…
Microsoft Excel 2016 Functions
SQRT:TheExcelSqrtfunctioncalculatesthe
positivesquarerootofasuppliednumber.
The syntax of the function is:
SQRT (number)

61. Contd…
Microsoft Excel 2016 Functions
Subtotal:TheExcelSUBTOTALfunctionperformsa
specifiedcalculation(eg.Thesum,product,average,
etc.)forasuppliedsetofvalues.
Theformatofthefunctionis:
SUBTOTAL (function_num, ref1, [ref2], …)
Function_num-Anumbertheatdenotesthecalculation
type(eg,sum,product,average,etc.)
Ref1,[Ref2],…-Oneormorereferencestocells
containingthevaluesthatthecalculationistobe
performedon.

62. Contd…
Microsoft Excel 2016 Functions
Function_num
(include hidden values)
Function_num
(ignore hidden values)
Function
1 101 Average
2 102 Count
3 103 Counta
4 104 Max
5 105 Min
6 106 Product
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

63. Contd…
Microsoft Excel 2016 Functions

64. Contd…
Microsoft Excel 2016 Functions
Round:
TheExcelROUNDfunctionroundsasuppliednumberup
ordown,toaspecifiednumberofdecimalplaces.
The format of the function is:
ROUND (number, num_digits)

65. Contd…
Microsoft Excel 2016 Functions
Roundup:TheExcelROUNDUP functionroundsa
suppliednumberup,awayfromzero,toaspecified
numberofdecimalplaces.
Theformatofthefunctionis:
ROUNDUP (number, num_digits)
Rounddown:TheExcelROUNDDOWN functionrounds
asuppliednumberdown,towardzero,toaspecified
numberofdecimalplaces.
Theformatofthefunctionis:
ROUNDDOWN (number, num_digits)

66. Contd…
Microsoft Excel 2016 Functions

67. Contd…
Microsoft Excel 2016 Functions
Trunc:TheExcelTRUNCfunctionindicatesa
suppliednumberbyremovingthedecimalpartof
thenumber.
The format of the function is:
TRUNC (number)

68. Contd…
Microsoft Excel 2016 Functions
SUM:TheExcelSUMfunctionsaddstogethera
suppliedsetofnumbersandreturnsthesumofthese
values.
The syntax of the function is:
SUM (number1, {number2}, …)

69. Contd…
Microsoft Excel 2016 Functions
Sumif: The Excel function finds values in a supplied
array, that satisfy a given criteria, and returns the sum of
the corresponding values in a second supplied array.
The syntax of the function is:
SUMIF (range, criteria, [sum_range]}

70. Contd…
Microsoft Excel 2016 Functions
Sumifs:TheExcelSumifsfunctionfindsvaluesinone
ormoresuppliedarrays,thatsatisfyasetofcriteria,and
returnsthesumofthecorrespondingvaluesinafurther
suppliedarray.
ThefunctionisnewinExcel2007,andsoisnotavailable
inearlierversionsofExcel.
Thesyntaxofthefunctionis:
SUMIFS (sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2], …)

71. Contd…
Microsoft Excel 2016 Functions

72. Contd…
Microsoft Excel 2016 Functions
Sumproduct: The Excel Sumproductfunction returns
the sum of the products of the corresponding values in
two or more supplied arrays.
The format of the function is:
SUMPRODUCT (array1, array2, [array3], …)

73. Contd…
Microsoft Excel 2016 Functions
Sumsq:TheExcelSumsqfunctionreturnsthesumof
squaresofasuppliedsetofvalues.
Theformatofthefunctionis:
SUMSQ (number1, [number2], …)

74. Contd…
Microsoft Excel 2016 Functions

75. Contd…
Microsoft Excel 2016 Functions

76. Contd…
Microsoft Excel 2016 Functions

77. Contd…
Microsoft Excel 2016 Functions
Degrees: The Excel Degrees function converts
radians into degrees.
The format of the function is:
DEGREES (Angle)

78. Contd…
Microsoft Excel 2016 Functions
Radians:TheExcelRadiansfunctionconverts
degreesintoradians.
The format of the function is:
RADIANS (Angle)

79. Text Functions
Microsoft Excel 2016 Functions
Clean:TheExcelCleanfunctionremovesall
nonprintablecharactersfromthetext.
Thesyntaxofthefunctionis:
CLEAN (text)

80. Contd…
Microsoft Excel 2016 Functions
Trim:TheExcelTRIMfunctiontakesasuppliedtext
stringandremovesanyspaces,exceptforsinglespaces
betweenwordsorcharacters.
Thesyntaxofthefunctionis:
TRIM (text)

81. Contd…
Microsoft Excel 2016 Functions
Dollar:TheExcelDOLLAR functionroundsa
suppliednumbertoaspecifiednumberofdecimal
placesandthenconvertsthisintotext,usinga
currencyformat.
Theformatofthefunctionis:
DOLLAR (number, [decimals])

82. Contd…
Microsoft Excel 2016 Functions
Fixed:TheExcelFIXEDfunctionroundsasupplied
numbertoaspecifiednumberofdecimalplacesand
thenconvertsthisintotext.
Theformatofthefunctionis:
FIXED (number, [decimals], [no_commas])

83. Contd…
Microsoft Excel 2016 Functions
Lower:TheExcelLOWERfunctionconvertsall
charactersinasuppliedtextstringtolowercase.
Theformatofthefunctionis:
LOWER (text)

84. Contd…
Microsoft Excel 2016 Functions
Text:TheExcelTEXTfunctionconvertsasupplied
valueintotext,inauser-specifiedformat.
Theformatofthefunctionis:
TEXT (value, format_text)

85. Contd…
Microsoft Excel 2016 Functions
Proper:TheExcelPROPERfunctionconvertsall
charactersinasuppliedtextstringtopropercase(ex.
Alllettersthatdonotfollowanotherletteraresetto
uppercaseandallothercharactersarelowercase).
Theformatofthefunctionis:
PROPER (text)

86. Contd…
Microsoft Excel 2016 Functions
Upper:TheExcelUPPERfunctionconvertsall
charactersinasuppliedtextstringtouppercase.
Theformatofthefunctionis:
UPPER (text)

87. Contd…
Microsoft Excel 2016 Functions
Concatenate:TheExcelCONCATENATE function
joinstogetheraseriesofsuppliedtextstringsorother
values,intoonecombinedtextstring.
Theformatofthefunctionis:
CONCATENATE (text1, [text2], …)

88. Contd…
Microsoft Excel 2016 Functions
Left:TheExcelLEFTfunctionreturnsaspecified
numberofcharactersfromthestartofasuppliedtext
string.
Thesyntaxofthefunctionis:
LEFT (text, [num_chars])

89. Contd…
Microsoft Excel 2016 Functions
Mid:TheExcelMIDfunctionreturnsaspecifiednumber
ofcharactersfromthemiddleofasuppliedtextstring.
Theformatofthefunctionis:
MID (text, start_num, num_chars)

90. Contd…
Microsoft Excel 2016 Functions
Right:TheExcelRIGHTfunctionreturnsaspecified
numberofcharactersfromtheendofasuppliedtext
string.
Thesyntaxofthefunctionis:
RIGHT (text, [num_chars])

91. Contd…
Microsoft Excel 2016 Functions
Rept:TheExcelREPTfunctionreturnsasuppliedtext
string,repeatedaspecifiednumberoftimes.
Theformatofthefunctionis:
REPT (text, number_times)

92. Contd…
Microsoft Excel 2016 Functions
Len:TheExcelLENfunctionreturnsthelengthofa
suppliedtextstring.
Theformatofthefunctionis:
LEN (text)

93. Contd…
Microsoft Excel 2016 Functions
Find:TheExcelFINDfunctionreturnsthepositionofa
specifiedcharacterorstringwithinasuppliedtextstring.
Thefunctioniscase-sensitive.Ifyouwanttoperforma
non-case-sensitiveSearch,usetheExcelSearchfunction
instead.
ThesyntaxoftheFindfunctionis:
FIND (find_text, within_text, [start_num])

94. Contd…
Microsoft Excel 2016 Functions
Exact:TheExcelEXACTfunctiontestsiftwosupplied
textstringsareexactlyequalandifso,returnsTRUE;
Otherwise,thefunctionreturnsFALSE,thefunctionis
case-sensitive.
Theformatoftheexactfunctionis:
EXACT (text1, text2)

95. Contd…
Microsoft Excel 2016 Functions
T:TheExcelTfunctiontextsifasuppliedvalueistext
andifso,returnsthesuppliedtext;Otherwise,the
functionreturnsanemptytextstring.
The format of the function is:
T (value)

96. Database Functions
Microsoft Excel 2016 Functions
Daverage:TheExcelDAVERAGEfunctioncalculates
theaverageofvaluesinafield(column)inadatabase
forselectedrecordsthatsatisfyuser-specified
criteria.
ThefunctionisverysimilartotheExcelAverageifs
function,whichisnewtoExcel2007.
ThesyntaxoftheExcelDaveragefunctionis:
DAVERAGE (database, field, criteria)

97. Contd…
Microsoft Excel 2016 Functions

98. Contd…
Microsoft Excel 2016 Functions
Dcount:TheExcelDCOUNTfunctionreturnsthe
numberofcellscontainingnumbers,inafield
(column)ofadatabaseforselectedrecordsonly.
Therecordstobeincludedinthecountarethosethat
satisfyasetofoneormoreuser-specifiedcriteria.
ThefunctionisverysimilartotheExcelCountifs
function,whichisnewtoExcel2007.
ThesyntaxoftheExcelDcountfunctionis:
DCOUNT (database, [field], criteria)

99. Contd…
Microsoft Excel 2016 Functions

100. Contd…
Microsoft Excel 2016 Functions
Dcounta:TheExcelDCOUNTAfunctionreturnsthe
numberofnon-blankcells,inafield(column)ofa
databaseforselectedrecordsonly.
Therecordstobeincludedinthecountarethosethat
satisfyasetofoneormoreuser-specifiedcriteria.
ThefunctionisverysimilartotheExcelDcount
function,theonlydifferencebeingthattheDcounta
functioncountsallnon-blankcellswheretheDcount
functiononlycountscellscontainingnumericalvalues.
ThesyntaxoftheExcelDcountafunctionis:
DCOUNTA (database, [field], criteria)

101. Contd…
Microsoft Excel 2016 Functions

102. Contd…
Microsoft Excel 2016 Functions
Dget:TheExcelDGETfunctionreturnsthevalue
containedinafield(column)ofasinglerecordina
database.
Therecordisselectedviaasetofoneormoreuser-
specifiedcriteria.
Thesyntaxofthefunctionis:
DGET (database, field, criteria)

103. Contd…
Microsoft Excel 2016 Functions

104. Contd…
Microsoft Excel 2016 Functions
Dmax:TheExcelDMAXfunctionfindsthemaximum
valueinafield(column)inadatabaseforselected
recordsonly.Therecordstobeincludedinthe
calculationaredefinedbyasetofoneormoreuser-
specifiedcriteria.
Thesyntaxofthefunctionis:
DMAX (database, field, criteria)

105. Contd…
Microsoft Excel 2016 Functions

106. Contd…
Microsoft Excel 2016 Functions
Dmin:TheExcelDMINfunctionfindstheminimum
valueinafield(column)inadatabaseforselected
recordsonly.
Therecordstobeincludedinthecalculationare
definedbyasetofoneormoreuser-specifiedcriteria.
Thesyntaxofthefunctionis:
DMIN (database, field, criteria)

107. Contd…
Microsoft Excel 2016 Functions

108. Contd…
Microsoft Excel 2016 Functions
Dproduct:TheExcelDPRODUCTfunctioncalculates
theproductofafield(column)inadatabasefor
selectedrecords,thatsatisfyuser-specifiedcriteria.
Thesyntaxofthefunctionis:
DPRODUCT (database, field, criteria)

109. Contd…
Microsoft Excel 2016 Functions

110. Contd…
Microsoft Excel 2016 Functions
Dsum:TheExcelDSUMfunctioncalculatesthesum
ofafield(column)inadatabaseforselectedrecords,
thatsatisfyuser-specifiedcriteria.
ThefunctionisverysimilartotheExcelSumifsfunction,
whichisnewtoExcel2007.
ThesyntaxoftheExcelDsumfunctionis:
DSUM (database, field, criteria)

111. Contd…
Microsoft Excel 2016 Functions

112. Logical Functions
Microsoft Excel 2016 Functions
AND:TheExcelANDfunctiontestsanumberofuser-
definedconditionsandreturnsaresultof:
-TRUE:ifALLoftheconditionsevaluatetoTRUE
or
-FALSE:otherwise(i.e.ifANYoftheconditionsevaluate
toFALSE)
Thesyntaxofthefunctionis:
AND (logical_test1, [logical_test2], …)

113. Contd…
Microsoft Excel 2016 Functions
OR:TheExcelORfunctiontestsanumberof
suppliedconditionsandreturnseither:
-TRUEifANYoftheconditionsevaluatetoTRUE
-FALSEotherwise(i.e.ifALLoftheconditionsevaluate
toFALSE)
Thesyntaxofthefunctionis:
OR (logical-test1, [logical_test2], …)

114. Contd…
Microsoft Excel 2016 Functions
NOT:TheExcelNOTfunctionreceivesalogicalvalue
andsimplyreturnstheoppositelogicalvalue.If
suppliedwiththevalueTRUE,theNOTfunction
returnsFALSEandifsuppliedwiththevalueFALSE,
thefunctionwillreturnthevalueTRUE.
Thesyntaxofthefunctionis:
NOT (logical)

115. Contd…
Microsoft Excel 2016 Functions
IF:TheExcelIFfunctiontestsauser-defined
conditionandreturnsoneresultiftheconditionis
true,andanotherresultiftheconditionisfalse.The
syntaxofthefunctionis:
IF (logical_test, value_if_true, value_if_false)
Where the arguments are as follows:
logical_test:Theuser-definedconditionthatistobe
testedandevaluatedaseitherTRUEorFALSE
value_if_true:Theresultthatistobereturnedfromthe
functionifthesuppliedlogical_testevaluatestoTRUE.
value_if_false:Theresultthatistobereturnedfromthe
functionifthesuppliedlogical_testevaluatestoFALSE.

116. Contd…
Microsoft Excel 2016 Functions
Nesting the Excel IF Function
TheIFfunctionisfrequently‘nested’inExcel.i.e.the
value_if_trueorthevalue_if_falseargumentis
replacedwithanothercalltotheIFfunction(see
Example3below).
Excel2003allowsupto7levelsofnestedIF
functions,butExcel2007andExcel2010allowupto
64levelsofnesting.ForExample,thefollowing
formula(whichhas8levelsofnesting),willresultin
anerrorinExcel2003butwillworkcorrectlyinExcel
2007orExcel2010.

117. Contd…
Microsoft Excel 2016 Functions

118. Lookup & Reference Functions
Microsoft Excel 2016 Functions
Lookup:TheExcelLOOKUP function‘looksup’a
suppliedvaluewithinadatasetandreturnsthe
correspondingvaluefromaseconddataset.ifthe
functionfailstofindanexactmatchtothesupplied
‘lookup’value,itusesthelargestvaluethatislessthanor
equaltothesupplied‘lookup’value.
Theformatofthefunctionis:
LOOKUP (lookup_value, lookup_vector,
result_vector)

119. Contd…
Microsoft Excel 2016 Functions
Hlookup:TheExcelHlookupfunction‘looksup’a
givenvalueinthetoprowofadataarray(ortable),
andreturnsthecorrespondingvaluefromanotherrow
ofthearray.
The syntax of the function is:
HLOOKUP (lookup_value, table_array,
row_index_num, [range_lookup] )
Where the function arguments are as follows on next
slide…

120. Contd…
Microsoft Excel 2016 Functions

121. Contd…
Microsoft Excel 2016 Functions
Vlookup:TheExcelVlookupfunction‘looksup’a
givenvalueintheleft-handcolumnofadataarray
(ortable),andreturnsthecorrespondingvalue
fromanothercolumnofthearray.
The format of the function is:
VLOOKUP (lookup_value, table_array, col-
index_num, [range_lookup] )

122. Contd…
Microsoft Excel 2016 Functions

123. Contd…
Microsoft Excel 2016 Functions
Index:Thearrayformatoftheindexfunctionisthe
mostbasicformofthefunction,andisusedwhenyou
wanttolookupareferencetoacellwithinasingle
range.
Thesyntaxofthefunctionis:
INDEX (array, row_num, col_num)

124. Contd…
Microsoft Excel 2016 Functions

125. Contd…
Microsoft Excel 2016 Functions
Areas:TheExcelAreasfunctiontakesanExcel
referenceandreturnsthenumberofareasthatmake
upthereference.
The format of the function is:
AREAS (Reference )

126. Contd…
Microsoft Excel 2016 Functions
Choose:TheExcelChoosefunctionretrnsavalue
fromalist,thatcorrespondstoasuppliedindex
number.
itmayhelptothinkoftheChoosefunctionasafunction
thatreturnsthenthentryinagivenlist.
The syntax of the function is:
CHOOSE (index_num, value1, [value2], …)
Wheretheargumentsareasfollows:
index_num:Anintegervalue,whichspecifiestheindex
oftheitemtobereturned.
value1,[value2],…:Alistofoneormorevaluesthat
youwanttoreturnavaluefrom.

127. Contd…
Microsoft Excel 2016 Functions
Transpose:TheExcelTranspose function
‘transposes’anarrayofcells(ie.Thefunctioncopies
ahorizontalrangeofcellsintoaverticalrangeand
viceversa).
Toinputanarrayformula,youneedtofirsthighlightthe
rangeofcellsforthefunctionresult.Typeyourfunction
intothefirstcelloftherange,andpress
CTRL+SHIFT+Enter.
The format of the function is:
TRANSPOSE (array)

128. Contd…
Microsoft Excel 2016 Functions

129. Contd…
Microsoft Excel 2016 Functions
Row:TheExcelRowfunctionreturnsthefirstrow
numberwithinasuppliedreference,orifnoreference
issupplied,thefunctionreturnsthenumberofthe
currentrowinthecurrentlyactiveExcelspreadsheet.
The syntax of the Row function is:
ROW ( [reference] )

130. Contd…
Microsoft Excel 2016 Functions
Rows:TheExcelRowsfunctiontakesanExcel
Rangeandreturnsthenumberofrowsthatare
containedwithintherange.
Thesyntaxofthefunctionis:
ROWS (array)

131. Contd…
Microsoft Excel 2016 Functions
Column: The Excel Column function returns the first
column number within a supplied reference, or if no
reference is supplied, the function returns the number
of the current column in the currently active Excel
Spreadsheet.
The syntax of the Column function is:
COLUMN ( [reference] )

132. Contd…
Microsoft Excel 2016 Functions
Columns:TheExcelColumnsfunctionreceivesan
Excelrangeandreturnsthenumberofcolumnsthat
arecontainedwiththerange.
Thesyntaxofthefunctionis:
COLUMNS ( array )

133. Financial Functions
Microsoft Excel 2016 Functions
Terms
•TheRateargumentisafixedpercentvalueapplied
duringthelifeoftheloan.
•ThePresentValueisthecurrentvalueoftheloanor
investment.Itcouldbethemarkedvalueofthecar,the
currentmortgagevalueofahouse,orthecash
amountthatabankislending.
•TheFutureValueisthevaluetheloanorinvestment
willhavewhentheloanispaidoff.
•TheNPeriodsisthenumberofperiodsthatoccur
duringthelifetimeoftheloan.Forexample,ifacaris
financedin5years,thisvaluewouldbe(5years*12
monthseach=60months).Whenpassingthis
argument,youmustremembertopasstheright
amount.

134. Contd…
Microsoft Excel 2016 Functions
FV (Future Value): to calculate the future value of
an investment, you can use the FV() function.
The syntax of this function is:
FV (Rate, Periods, Payment, PresentValue,
PaymentType)

135. Contd…
Microsoft Excel 2016 Functions
NPER(NumberofPeriods):Tocalculatethe
numberofperiodsofaninvestmentoraloan,youcan
usetheNPER()function.
Itssyntaxis:
NPER (Rate, Payment, PresentValue,
FutureValue, PaymentType)

136. Contd…
Microsoft Excel 2016 Functions
PMT(InvestmentorLoanPayment):ThePMT()
functionisusedtocalculatetheregularpaymentof
loanoraninvestment.
itssyntaxis:
PMT (Rate, NPeriods, PresentValue,
FutureValue, PaymentType)
Inthefollowingexample,acustorerisapplyingfora
carloan.ThecostofthecarwillbeenteredincellB3.It
willbefinancedatarateenteredincellB5foraperiod
setincellB6.

137. Contd…
Microsoft Excel 2016 Functions
Supposethat,duringtheevaluation,acustomerdecidesthat
shedoesn’tneedabrandnewcaranymore.Also,shethinks
thata5-yearcarloanistoolong.Furthermore,shewantsto
makea$4500.00downpaymenttoreducethemonthly
payments.Ontheothersideofthedesk,thesalespersonwho
wantstomakeajuicycommissiononthisloanhasdecidedto
increasetheinterestrate.

138. Contd…
Microsoft Excel 2016 Functions
Changethenewvaluesoftheworksheetasfollows
andseetheresult.

139. Contd…
Microsoft Excel 2016 Functions
The Amount Paid as Interest During a Period:
Whenacustomerisapplyingforaloananinvestment
companymustbeveryinterestedtoknowhowmuchmoneyit
wouldcollectasinterest.Thisallowsthecompanytoknow
whethertheloanisworthgiving.Becausetheinterestearnedis
relatedtotheinterestrate,acompanycanplaywiththerate
(andalsothelength)oftheloantogetafair(?)amount.
-TheIPMT()functionisusedtocalculatetheamountpaidas
interestonaloanduringaperiodofthelifetimeofaloanoran
investment.Itisimportanttounderstandwhattisfunction
calculates.Supposeacustomerisapplyingforacarloanand
thesalespersondecide(oragreeswiththecustomer)thatthe
loanwillbespreadover5years(5years*12monthseach=60
months).Thesalespersonthenappliesacertaininterestrate.

140. Contd…
Microsoft Excel 2016 Functions
IPMT:TheIPMT()functioncanhelpyoucalculatethe
amountofinterestthatthelendinginstitutionwould
earnduringacertainperiod.Inessence,youcanuse
ittoknowhowmuchmoneythecompanywouldearn
inthe3
rd
year,orinthe4
th
year,orinthe1
st
year.
Basedonthis,thisfunctionhasanargumentcalled
Period,whichspecifiestheyearyouwanttofindout
theinterestearnedin.
The syntax of the IPMT() function is:
IPMT (Rate, Period, Nperiods, PresentValue,
FutureValue, PaymentType)

141. Contd…
Microsoft Excel 2016 Functions

142. Contd…
Microsoft Excel 2016 Functions
The Amount Paid as Principal:
WhiletheIPMT()functioncalculatestheamountpaidas
interestforaperiodofaloanoraninvestment,the
PPMT()functioncalculatestheactualamountthat
appliestothebalanceoftheloan.Thisisreferredtoas
theprincipal.
Its syntax is:
PPMT (Rate, Period, Nperiods, PresentValue,
FutureValue, PaymentType)

143. Contd…
Microsoft Excel 2016 Functions
Theargumentsarethesameasdescribedinthe
previoussections.

144. Contd…
Microsoft Excel 2016 Functions
The Interest Rate:
Supposeacustomercomestoacardealerandwantstobuya
car.Thesalespersonwouldfirstpresenttheavailablecarsto
thecustomersothecustomercandecidewhatcarhelikes.
Afterthisprocessandduringtheevaluation,thesalesperson
maytellthecustomerthatthemonthlypaymentswouldbe
$384.48.Thecustomermaythensay,“Wooooh,Ican’tafford
that,man”.Thenthesalespersonwouldask,“Whattypeof
monthlypaymentsuitsyou”.Fromnowon,bothwould
continuethediscussion.Sincethesalespersonstillwantsto
makesomemoneybutwithoutlosingthecustomerbecauseofa
highmonthlypayment,thesalespersonwouldneedtofinda
reasonableratethatcanaccommodateanaffordablemonthly
paymentforthecustomer.

145. Contd…
Microsoft Excel 2016 Functions
TheRATE()functionisusedtocalculatetheinterest
appliedonaloanoraninvestment.
Itssyntaxis:
RATE (NPeriods, Payment, PresentValue,
FutureValue, PaymentType, Guess)

146. Contd…
Microsoft Excel 2016 Functions
IRR(TheInternalRateofReturn):TheIRR()
functionisusedtocalculateaninternalrateofreturn
basedonaseriesofinvestments.
Itssyntaxis:
IRR (Values, Guess)

147. Contd…
Microsoft Excel 2016 Functions
Thevaluesargumentisaseries(alsocalledanarrayora
collection)ofcashamountsthatacustomerhasmadeonan
investment.Forexample,acustomercouldmakemonthly
depositsinasavingsorcreditunionaccount.
Anothercustomercouldberunningabusinessandreceiving
differentamountsofmoneyasthebusinessisflowing(orlosing
money).Thecashflowsdon’thavetobethesameatdifferent
intervalsbuttheyshould(ormust)occuratregularintervalssuch
asweekly(amountcutfromapaycheck),bi-weekly(401kdirectly
cutfrompaycheckmonthly(regularinvestment),oryearly
(income).
Thevaluesargumentmustbepassedasacollectionofvalues,
suchasarangeofselectedcells,andnotanamount.Otherwise
youwouldreceiveanerror.
TheGuessparameterisanestimateinterestrateofreturnofthe
investment.

148. Contd…
Microsoft Excel 2016 Functions
NPVNetPresentValue:TheNPV()functionusesa
seriesofcashflowstocalculatethepresentvalueof
aninvestment.
Itssyntaxis:
NPV (Rate, Value1, Value2, …)

149. Contd…
Microsoft Excel 2016 Functions
TheRateparameteristherateofdiscountduringone
periodoftheinvestment.
AstheNPV()functiondoesn’ttakeafixednumberof
arguments,youcanaddaseriesofvaluesasValue1,
Value2,etc.Theseareregularlymadepaymentsfor
eachperiodinvolved.Becausethisfunctionusesa
seriesofpayments,anypaymentmadeinthepast
shouldhaveapositivevalue(becauseitwasmade
already).Anyfuturepaymentshouldhaveanegative
value(becauseithasnotbeenmadeyet).

150. Convert Functions
Microsoft Excel 2016 Functions
WeightConvert:Convertsanumberfromone
measurement systemtoanother.Forexample,
CONVERTcantranslateatableofweightingramtoa
tableofweightinslug.

151. Contd…
Microsoft Excel 2016 Functions
DistanceConvert:Convertsanumberfromone
measurement systemtoanother.Forexample,
CONVERTcantranslateatableofdistancesininchto
atableofdistancesinfoot.

152. Contd…
Microsoft Excel 2016 Functions
TimeConvert:Convertsanumberfromone
measurement systemtoanother,ForExample,
CONVERTcantranslateatableoftimeinyeartoa
tableoftimeinhour.

153. Contd…
Microsoft Excel 2016 Functions
TemperatureConvert:Convertsanumberfrom
onemeasurementsystemtoanother.Forexample,
CONVERT cantranslateatableoftemperaturein
CelsiustoatableoftemperatureinFahrenheit.

154. Array Functions (Database)
Microsoft Excel 2016 Functions
Anarrayformulaisaformulathatworkswithan
array,orseries,ofdatavaluesratherthanasingle
datavalue.
SUMIF, COUNTIF AND AVERAGEIF

155. Contd…
Microsoft Excel 2016 FunctionsS#Staf's Full NamePosition Salary Workplace _ Main Fsub
1Fareed, HabibiAdmin 25,000؋Main Salary 10800070000
2Momin, KarimiVice 10,000؋Fsub # of Employees 11 7
3Fawad, AtayeeCoordinator 12,000؋Main Salary Average9818.18210000
4Aziz, Azizi ICT Teacher 11,000؋Main
5Haroon, AmiriICT Teacher 8,000؋Main
6Ismail, FanosEnglish Teacher 12,000؋Fsub
7Asad, Sahei English Teacher 12,000؋Fsub
8Jawid Science Teacher 5,000؋Fsub
9Musawer Science Teacher 10,000؋Fsub
10Murad, AmaniScience Teacher 11,000؋Fsub
11Feroz, AmiriScience Teacher 10,000؋Fsub
12Sanaullah Science Teacher 12,000؋Main
13Musawer, SahilEnglish Teacher 9,000؋Main
14Fazulrabi Art Teacher 8,000؋Main
15Shabnum English Teacher 8,000؋Main
16Bakht Beik Guard 7,000؋Main
17Sebghat Guard 3,000؋Main
18Ekram Controller 5,000؋Main
Asman Institute's Main Database _ Main
Salary
=SUMIF(E3:E20,H2,D3:D20)
# of Employees
=COUNTIF(E3:E20,H2)
Salary Average
=AVERAGEIF(E3:E20,H2,D3:D20)