Excel is intended to be used to
calculate and analyze numerical
information such as household budgets
company finances inventory and more to
do this you need to understand formulas
in lesson five we cover simple formulas
that we have one mathematical operation
such as two plus two in this lesson
we'll discuss complex formulas that use
multiple mathematical operators and that
use absolute and relative references
this lesson may take you back to high
school math because in Excel there are
certain math rules that you have to
follow for formulas to work the order of
mathematical operations is very very
important if you enter a formula that
contains several operations like
addition subtraction multiplication and
division Excel knows to work these
operations in a specific order first in
that order is parenthesis operations
that are enclosed in parentheses are
completed first next we have exponents
calculations that involve exponents such
as two to the second power are completed
next I would guess that most of the
spreadsheets you work with will not use
exponents but this is part of the basic
mathematical order so it's worth
mentioning the next steps in the order
are multiplication and division these
two operations are considered to be
equal or at the same level this means
that if you have a formula with both a
multiplication
and a division sign Excel will read the
formula from left to right and perform
whichever operation comes first and
finally we have addition and subtraction
these two operations are considered
equal light multiplication and division
so Excel will read the formula from left
to right and perform whichever operation
comes first a mnemonic that can help you
remember this is please excuse my dear
Aunt Sally
I actually remember this from my ninth
grade math class
it stands for parentheses exponents
multiplication division addition and
subtraction like I said before you
probably won't have to deal with
exponents but it will help you remember
the order of operations now let's take a
look at how the order of operations will
affect your Excel formulas let's write a
formula with two operations as always we
start our formulas with an equal sign
now Excel will follow the order of
operations and will multiply two numbers
and then add so 3 times 2 equals 6 and 3
plus 6 equals 9 so I'll just click enter
and see yes the answer is 9 now what if
I want Excel to add 3 plus 3 together
before multiplying let's recite the
formula and add parentheses around 3
plus 3 following the order of operations
Excel will add the numbers and
parentheses first so we could have 3
plus 3 equals 6 and then multiply the
number times 2 to equal 12 let's check
and we can see that Excel followed the
order of operations before moving on
let's take a look at some formulas to
make sure you really understand the
order of operations which Excel uses to
calculate answers you really do have to
understand this so that you can write
formulas correctly we will multiply four
times two before performing the division
operation because the multiplication
sign it comes before the division sign
the answer is two what about this one
we'll divide 4 by two before performing
the multiplication operation because the
division sign comes before the
multiplication sign the answer is eight
now let's add parentheses and see how
that changes the problem we'll need to
perform the operation in parentheses two
times four first and divide four by this
result
the answer is 0.5 what about this one
multiply two times four before
performing the subtraction operation
because a multiplication sign is of a
higher order than the subtraction sign
the answer is negative four now that
we've covered the order of operations
and written a few formulas let's see how
it works in a spreadsheet using cell
references instead of numbers a cell
reference or cell address is the name of
the cell based on its location this cell
is where column G and row six intersect
so the cell reference or cell address is
G six now if you're ever unsure about
the cell address you can find it in the
name box which is to the left of the
formula bar
let's say we want to calculate how much
I'm spending per bowl let's write a
formula here in h6 and we'll just delete
it when we're done we'll insert an equal
sign to get started now what do we need
to do to calculate this since we only
have one case each of the small and
regular bowls we just need to add the
tool bowl amounts together to calculate
how much we've spent on bowls to do this
using cell references we'll select G 6
type an addition sign and then select G
7 to figure out how much we paid per
Bowl we'll need to divide by the total
amount of bowls that we have to
calculate how many bowls we have we'll
need to add cell d6 and d7 now can you
see a problem with this formula we want
to divide the total dollar amount we
spent by the total number of bowls so
that we can calculate how much we're
spending per bowl does this formula do
that for us no it really doesn't you see
with the way the formula is currently
written Excel will divide
g7 by d6 since the vision is completed
before addition this will give us some
big number that clearly doesn't indicate
how much we're paying per bowl let's add
some parentheses to the formula so that
Excel knows to perform the addition
operations before it divides and now we
can see that we're paying about 13 cents
per bowl if you want to show fewer
decimal places you can just click the
decrease decimal place command
on the Home tab I think we've covered
complex formulas using the order of
operations pretty well so let's talk
about two types of cell references
relative and absolute references the
formula in this cell takes the number
and f2 and multiplies it by the number
in situ but what the Excel program sees
when it reads this formula is that it
should multiply the value that appears
in one cell to the left by the value
that appears and for cells to the left
from where the formula appears this is
called a relative cell reference when
you copy a formula containing relative
references to another cell the relative
references automatically change so when
I drag this formula down and fill the
other cells with it you won't see F 2
times C 2 you'll see the new relative
references so here in G 3 the formula
reads the value in one cell to the left
times a value that appears in four cells
to the left or F 3 times C 3 and you can
see the same thing in these other cells
the other type of reference is called an
absolute cell reference if a formula is
copied to a different location the
absolute reference remains the same an
absolute reference is designated in the
formula by the addition of a dollar sign
it can proceed the column reference or
the row reference or both in this
example the column and the row do not
change when copied in this example the
row does not change when copied in this
example the column does not change when
copied
let's see how this might work in a
spreadsheet over here in h2 let's write
a formula that multiplies the inventory
number c2 if you want to create an
absolute reference we'll just add our
dollar signs before the C and before the
two and then press ENTER and we can see
that the amounts in G 2 and H 2 are the
same even though H 2 uses an absolute
reference so when I fill this formula in
to three additional cells I can see that
the numbers don't match up with what
appears in G 3 4 and 5 this formula
takes a number in F 3 and multiplies it
by the number in C to the C 2 will stay
constant because of the dollar signs
which make it an absolute reference this
spreadsheet doesn't need these formulas
so let's just delete them as you can see
the order of operations and relative and
absolute references are important to
understand if you plan on using formulas
in your spreadsheet spend some time
writing formulas and see how they work