[PDF]Excel 2016 Formulas Functions Training

Rylander Consulting


www.RylanderConsulting.com
sandy@rylanderconsulting.com


425.445.0064


WD Excel 2016: Formulas & Functions


Version 04092017_2042
© Rylander Consulting


Excel 2016: Formulas & Functions EQ)


Table of Contents


PUSAN PICHON S chet casa tianaisaiaaiadoa tsauamsahiad wean tetycaaeuniasuamchaaasmea sanaruenstaaadehaaansanaaeamianans 1
BPMN eh arya capa estar opens eeeea eed a anesrnctaee tara ornare aves recipe taptedansnasa coach aoemiee neat 1
lS QING SY MAN ass caceccaziaciadnseiihecnauasiadeniasaaastaaativxsaxamaais cdeavraadeniaddadnaiuasesudiadeaans 1
Relative versus Absolute Cell Referencing..........:::ccccsssccccssssseecesssnseeecssseeeecesseeeeeesseaaes 1
Functions — Make Calculations Easier Than Using FOrMulas...........:ccccssssccessssteesesseeeeees 2
SVAN esta rt even sate detavaveveuanetedeiaysvevenauavedel dbacdssuanateculagaadectanars culstuedecushdieeulesaaveetsiaes abaeeees 2

The AutoSum Tool oni the Heme tab ssc.cissisacciantecriarsuscaanatdancaann tiadaatacaanndennantanraans |
Insert Excel PURCTONS = 4 METNOS oaiseadwaciuraseorsganseindtiannessedeaawan sie aivadeesiavaderanssategeenes 5
[ASENE FUNCTION TOO ssi sasescansadipnantosesinaneaatiionsoauusanes sasnenbsnesieanaaainnnan tapaaunwennenntasuunenanne 6
MOG FUN CHONS ..sceccitenes cctcvivedteatt cocenetiigaidenpenedsevtdecidanwinedsant?sasleuuianedexddeedlieashenitertiecdbeuntand 10
LOI Call EE FUN CO ON sseceecint ce eh ceatsteutvatn te cebaasucies eee vs eraceeduisuleaautienbenailysastetaAwcdeemntescesneeeants 10
Logical AND Function combined Withi ll icici ccdcscaeacazscceacdiacsustecinavaccztsdact tout eddesnmnsdecins 10
Logical-OR Function: Combined: With Pispisccstccclsassapiicessicneesacadtenaandalentagdereisdasertadelemas 11
Concatenate Cells — Join Text Together..........cccccccsssscecssssneceessnseeecssseeeeceesseeeeeeseaeesensea 11
PERE NAS aa atest eeccsts ied carole ecco adateeevancdesusatae ebupeaed tu teebenvepasanets Aabadaas beans udgaewaa 12
Create a Range Name Individually ..........ececsscecsssseceeeessceceesenaeeecesseeeeesseeeeeeeeeaaeeeeees 12
Convert existing row and column labels to NAMES...........:cccccessseceeessseeeeeesteeeeeesenaeeeeeees 12
PaSt@: NAMES sicccsscavsscephid enti t veveaiesleassadeeavaveesbli tees iveasteaddianacaperee seatenreteeane est aebeleetenesis 13
Delete or Add or Modify Range Names............cccsscccccssssececsssseeecessseeceesenaeeecesseeeeeeesenaes 13
LOOP ais csrccareatateacqensohanacppnseinsaa atta teat adeienaancuasand alsa ginateanagactananaenndanaatecaameamdaaecas 15
SV tol VEO OR UP si citictdieencnepeneeeeranacholobdvaiad xatsenaddaennaanstianseddteaateasspatneeumnnerentatal 15
Worksheet PiNCtIOM ERAN ple isi. scccsiciasacsensiatlncbiadatecseastedadedwatuadazeiecsaadadusstecbadaniaundesgae 15
SUIIVAIE race sscivieentnnaniriceagueatentonetinniusssiieiutonnnsatadarrneeiesmhlaueea eden dep ech iau vn inoiunn- sedan ats ane moNenveNN 17
SYM OE SUMP -pistasiccsssne eipned ainsi unin scx cuainareyen gan ie yudunint yaasind diesabua wacynana ba paean naan sa peeanaiees 17
Worksheet FUNCTION: ENGI DIG soc ccsedctiiepsaceg tuner veedteeredentseddantensedsnnveepeenieanateavbiauniendadacs 17
Worksheet Function: Exainiple F2 siccccisicrcecesisiciccsdiedeecaichaseenbeeeueediaeensccdelunesidbosenauiieeels 17
Worksheet Function: Example 83 svn ccisssccacsssnseridsacdennescdsasudensterudsnedenndecdoanpacedeneuelanenubans 18


Version 04092017_2042
© Rylander Consulting


Excel 2016: Formulas & Functions EX)


Formulas and Functions


There is a whole new formula bar in Excel 2013 to make entering Formulas and
Functions easier.


HOME INSERT | PAGELAYOUT | FORMULAS | DATA REVIEW VIEW _ DEVELOPER


fe > |] Ig |2| A\ IB ig 8 | 1 S Define Name ~ Be Trace Precedents [Fé] Show Formulas Fal a r yeharae


“fk Use in Formula ot Trace Dependents “® Error Checking ~ ;
Insert | AutoSum Recently Financial Logical Text Date & Lookup & Math& More Name Watch | Calculation [2 Calculate Sheet


ra] (&)
Function ~ Used + . + Time Reference Trig> Functions~ | Manager E53 Create from Selection | T5Remove Arrows ~ (&) Evaluate Formula | window | Options ~


Function Library Defined Names Formula Auditing Calculation


Formulas


Rules and Syntax


“an


All Formulas or Functions start with an
Formulas use these operators (all of these operators can be found on the
numeric keypad) and are calculated in the following order:
dia Multiplication
ay” Division
uy? Addition
“an Subtraction
Example of a Formula and its Answer:
=5+4*2 would the answer be 18 or 13?
IMPORTANT: The answer would be 13 because the Mathematical
Hierarchy states the multiplication and division always occur before
addition and subtraction unless parenthesis are used. If parentheses are
used, that operation will override the default hierarchy. In other words,
if you wish the answer to be 18, the formula must be
= (5+4)*2.


Relative versus Absolute Cell Referencing


Relative Cell Reference
When you type a formula or function in a cell (like the one shown below —
B2+C2) you would then generally copy or fill that formula to the cells remaining
cells. If the formula were truly copied, each cell would contain =B2+C2 which, in
this case, would not be what you want. You would want Excel to increase the
row number for you as you copied the formula down to the other cells, which it
does! Notice that =B2+C2 becomes =B3+C3 and then =B4+C4 etc. This same
technique would increase/decrease column letters if you copied to the right or
left. Relative cell referencing is the default in Excel.


B Cc 0) E


1 January February Total
2 Ice Cream 100 10 =B2+C2 ,
3 Cones 150 12 =B34+C3 Notice how the row number changed as the
4 |Cherries 200 14 =B4+C4 formula was copied or filled down.
5 Nuts 250 16 =B5+C5
6 Total =SUM(B2:B5) =SUM(C2:C5) =SUM(D2:D5)


Version 04092017_2042
© Rylander Consulting


DI) Excel 2016: Formulas & Functions


Absolute Cell Reference
There are times, however that you do NOT want relative cell addressing.
Sometimes you need your reference to stay put. That is when Absolute cell
addressing comes into play. To make a cell reference absolute, press F4 in the
part of the formula you want to stay referencing the same cell. F4 makes an
address absolute by placing dollar signs (S) in front of the column letter and row
number (i.e. $BS4). This means that as you copy that formula, the reference to
SBS4 will not change.


B c D E
1 i January February Total
2 Ice Cream 100 10 =B2+C2
3 Cones 150 12 =B3+C3
4 Cherries 200 14 =B4+C4
5 |Nuts 250 16 =B5+C5
6 Total =SUM(B2:B5) =SUM(C2:C5) =SUM(D2:D5)
Relative - notice how the cell B? multiplies
B6*B10 which is correct, but when this
forrnula is copied into cells C? and D?, the
Tax Amount - second half of the formula B10, incorrectly
Incorrect changes to C10 and then D10 - which are
? Formula =B6*B10 =C6*C10 =D6*D10 blank (relative cell referencing)!
Absolute - notice how the cell BS multiplies
B6*B10 which is correct, and when you make
B10 absolute, by typing a $ before the B and
a $ before the 10 (or pressing F4 - the
absolute key), this formula stays correct when
Tax Amount - it is copied into cells C6 and D8. Notice the
Correct reference to $B$10stays $6510 (absolute cell
8 Formula =B6*$B310 =C6*$BS10 =D6*$BS10 referencing)!
93


10 Tax Rate 0.08
Functions - Make Calculations Easier Than Using Formulas


Example — add B5 through B10


Using a formula: =B5+B6+B7+B8+B9+B10
Using the formula to Add a Range: =SUM(B5:B10)


Syntax
The syntax of a function is generally “=function name(range)” see examples
below
Sum: =SUM(B5:B10)
Minimum: =MIN(B5:B10)
Maximum: =MAX(B5:B10)
Average: =AVERAGE(B5:B10)
Count: =COUNT(B5:B10)


Version 04092017_2042
© Rylander Consulting


Excel 2016: Formulas & Functions EX)


The AutoSum Tool on the Home tab


Sum a Column
Using the example below:


1. Click in cell B9 - the cell directly below the data.


2. Click the AutoSum tool.


Notice that the cells it thinks you want to add have a marquis, a dotted line,
around them.


3. If the cells you wish to add have a marquis around them, simply press Enter or
click the AutoSum tool again to remove the marquis.


__Complete Excel Training Workbook New.xlsm - Excel
HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER
% Cut DAutosum ~ +
Ea Copy ~ r F i R
Sacre & = ae s = Alignment = : Number Styles =" = ae Editi
CONCATENATE * ? | X WY fe | =SUM(B5:B8) eee mute
A B c D E |
4 _ January February March April fy | al
5 Ice Cream $ 1,000 $ 2,000 $ 3,000 $ 4,000 $ 5,000 $
6 Cherries 900 1,900 2,900 3,900 4,900
7 |Cones 800 ! 1,800 2,800 3,800 4,800
8 Nuts | 700} 1,700 2,700 3,700 4,700
9 |Total =SUM(B5:B8) <—
AN SUM(number1, [number2]
Sum Multiple Columns
4. Select all the cells where you want the totals to appear.
HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sandy Rylander > g
it Be ee Arial fe -|a |= =e | Rwep tet Custom . ie Ey ie eo Ee (i — i t; Hi
Paste ice Br u-|S-|-A- |= = =| =| Eimergeecenter -| $~ % » | 92 | Conditional Formatas Cell_| Inset Delete Format a Sort & Find &
- z Formatting ; he Styles aa Lie Select
SOR Hee oo 1B Xe aH *biS |HUMYIGS1B BAS SOsDs
bd Ih fe ——— cally add it up. Your total
A B Cc D = | 3 will appear after the selected cells.
4 January’ February March April 7; |i!
5 Ice Cream $ 1,000 $ 2,000 $ 3,000 $ 4,000 $ 10,000
6 Cherries 900 1,900 2,900 3,900 9,600
7 Cones 800 1,800 2,800 3,800 9,200
8 Nuts 700 1,700 2,700 3,700 8,800


9 |Total | <—
5. Click the AutoSum tool


(i.e. Select B9:F9 to sum all the rows at once, or F5:N9 to sum all the columns at
once.)


Version 04092017_2042
© Rylander Consulting


DI) Excel 2016: Formulas & Functions


Sum Rows and Columns Simultaneously
6. Select the data you wish to add plus one extra row and one extra column.
(so Excel knows where you want the totals to appear).


7. Click the AutoSum tool.
(i.e. in the example above, select B5:F9 to sum all the rows and columns at once.)


FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER


Sandy Rylander * €


rn ce Arial jz -|A ac |= =) e- | Rweptet Custom . Fa ie i =m ox fe] Eien ad Sy ih
* Gromannen |* FH -1Si-[a-A-|= = |e ae] Enemmcnie «| $6» [ok a | ced ee | en orem OO se etn
Clipboard & Font & Alignment & Number G& Styles Cells Editing
=m ORR &®& | 6- BX) aie Pls INU AY BAB BASlaeasy7 -
Pa “Ee & | 1000 Sum (Alt+=)
A B c D E :
5
5!
4 January February March April Ty | ul
5 |lce Cream $ 1,000'|$ 2,000 $ 3,000 $ 4,000
6 |Cherries 900 1,900 2,900 3,900
7 |Cones 800 1,800 2,800 3,800
8 |Nuts 700 1,700 2,700 3,700 t
9 |Total


Tip! If there are any gaps in your data (i.e. blank cells) it is a good idea to select
the data in addition to the cell where you want the total to appear, before
clicking the AutoSum tool. This will cause Excel to include all highlighted
cells in the total, rather than stopping at the first blank cell. See example
below.


Selecting only B9 before hitting Autosum includes


Selecting B3:B7 before hitting Autosum includes
only data up to the first blank cell in total.


all data in total.


A B A B
4 January | 4

5 Ice Cream $ 1,000 4 5 |\Ice Cream

6 Cherries 6 |Cherries

7 Cones 7 7 |\Cones

8 Nuts | foams! 700 | 8 |Nuts

9 |Total =SUM(87:88) 9 |Total


Version 04092017_2042
© Rylander Consulting


Excel 2016: Formulas & Functions EX)


Insert Excel Functions - 4 Methods


=" The More Functions Option on the AutoSum tool


=" The Insert Function tool on the left side of the Formula bar


=" The Function Library on the Formulas bar


= Typing an equals and then using Excel’s formula entry help.


AutoSum: More Functions


> sum


Insert Function on the Formula Bar


> te
Average Ly
Count Numbers — =n ; insert Function ; E , F , G
Max
Min
More Functions...
Function Library on the Formula Tab
HOME INSERT | PAGELAYOUT | FORMULAS | DATA _ REVIEW


fi. OB BeaDeE ROE


Insert | AutoSum Recently Financial Logical Text Date & Lookup & Math& More


Used + Sa ’ 7
Function Library


Function ’


Start Typing Function in Cell


7] ® counta
@ COUNTBLANK
Z| (@@ COUNTIF
@@ COUNTIFS
@@ COUPDAYBS
@ COUPDAYS
(@@ COUPDAYSNC
E @ CouPNCD
@ COUPNUM
— @ couppcp


Version 04092017_2042
© Rylander Consulting


Time Referencey Trig* Functions ~


Counts the number of cells in a range that contain numbers


DW sExcel 2016: Formulas & Functions


Insert Function Tool


If you do not know how to enter a Function:
1. Click in the cell where you want the Function to appear and click either:
a. the Insert Function tool on the Formula bar; or


b. The Insert Function tool on the Formula tab.


2. The Insert Function Dialog box appears, allowing you to select any of Excel’s
functions.


a. You can search for a function by typing in a description of what you are
wanting to find; or


Search for a function:


Type a brief description of what you want to do and then Go + To search for a function, type a
| click Gol description and press Go.


Or select a category: | Most Recently Used


Select a function:


CONCATENATE
IF

VLOOKUP
COUNTA
COUNT
AVERAGE


PMT
CONCATENATE(text1,text2,...) A definition will help you
determine if the function


Joins several text strings into one text string. sdlectedis shevoneyalneed


Help on this function


b. You can search a reduced number of functions by selecting a category.


Version 04092017_2042
© Rylander Consulting


Excel 2016: Formulas & Functions


Search for a function:


Type a brief description of what you want to do and then
click Go


Select a Function Category to

BISTERECENURE reduce the number of functions
Select a function: displayed or select All to view all
functions.


Or select a category: Most Recently Used


CONCATENATE


Information
Joins several text stri{ Engineering


Help on this function q——


Version 04092017_2042
© Rylander Consulting


DIE) Excel 2016: Formulas & Functions


3. Once you find the desired function, you can press Help to get great descriptions
and examples of how to correctly use this function. Here is a great example of
the Help for the Count function. It gives you a description of the function,
followed by its syntax, other remarks, and 1 to 5 examples of how to use it ina


worksheet!


COUNT


Syntax


COUNT(value4,value2,...)


Remarks


Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers.


Value1, value2, ... are 1 to 256 arguments that can contain or refer to a variety of different types of data, but only numbers are counted


= Arguments that are numbers, dates, or text representation of numbers are counted


= Logical values and text representations of numbers that you type directly into the list of arguments are counted.


= Arguments that are error values or text that cannot be translated into numbers are ignored


= If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored


= If you want to count logical values, text, or error values, use the COUNTA function


Example


The example may be e,


Howto copy an example


A
Data


Sales


12/8/2008


19
22.24
TRUE


oY Ge wWH


#DIVO!


=COUNT(A2:A8)
=COUNT(AS:A8)


Formula Description (Result)


Counts the number of cells that contain numbers in the list above (2)
Counts the number of cells that contain numbers in the last 4 rows of the list (2)


=COUNT(A2:A8,2) Counts the number of cells that contain numbers in the list, and the value 2 (4)


4. When you are done reading Help, you can click the “X” in the top right corner of
the title bar. This brings you back to the Insert Function dialog box. If you then
click OK, Excel will step you through inserting the Arguments as shown below.


Click OK when done.


Search for a Function:
Type a brief description of what you want to do and then click Go


Go
Or select a category: Most Recently Used Ir]


Select a function:


IF

SUM

AVERAGE

HYPERLINK

MAX

SIN oF:


COUNT(value1,value2,...)
Counts the number of cells in a range that contain numbers.


Mm) >


Version 04092017_2042
© Rylander Consulting


(Fa) = {500;500;500;500;500;500}
(Ea) = number


t Collapses the dialog box.


Counts the number of cells in a range that contain numbers.


¥Yaluel: yalue1,value2,.., are 1 to 255 arguments that can contain or refer to a
variety of different types of data, but only numbers are counted,


Formula result = 6


Help on this function


Excel 2016: Formulas & Functions EX)


=" Here is another example of using Insert Function to help with the Sum Function.


1. Click in the cell where the sum is to appear.


2. Click the Insert Function tool on the formula bar.


3. Select Sum for the function and click OK. The following dialog box appears.


SUM yOxXKY =SUM(B4:87) <————

B.A. 8 a a YT J
1_Baskin Robbins

ie

[3

[4

5

6 SUM

7


a|a)o)o)ofo/olo
Aan Roy ase ©


Formula result = 3400


Help on this function


vinixixisis
w —_~ow oO


Numberl 84:87
Number2


Adds all the numbers in a range of cells.


= {1000;900;800;700}
=


number


= 3400


Number1: numberi,number2,.,. are 1 to 255 numbers to sum. Logical values and text
are ignored in cells, induded if typed as arguments.


(Co) (_ conca_}


4. The Function Name and the ( )s appear in the formula bar. Either type in the
desired range in the Number 1 text box; or


5. Make the dialog box collapse by clicking on the tool shown below and then
drag across the range you want to select in the worksheet.


Your screen should look like this:


Ice Cream
>>>