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 5, we cover simple formulas that
have one mathematical operation, such as 2+2. 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 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 parentheses. Operations that are enclosed in parentheses
are completed first. Next we have exponents.
Calculations that involve exponents, such as 2 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
like 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 2 operations. As always, we start our formulas with an equal
sign. Now, Excel will follow the order of operations
and will multiply the 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 nine.
Now, what if I want Excel to add 3 plus 3 together before multiplying? Let's retype
the formula. And add parentheses around 3 plus 3.
Following the order of operations, Excel will add the numbers in parentheses first, so we
have 3 plus 3 equals six, and then multiply that 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 4 times 2 before performing
the division operation because the multiplication sign comes before the division sign.
The answer is 2. What about this one? We'll divide 4 by 2 before
performing the multiplication operation because the division sign comes before the multiplication
sign. The answer is 8.
Now let's add parentheses and see how that changes the problem.
We'll need to perform the operation in parentheses (2*4) first and divide 4 by this result.
The answer is 0.5. What about this one? Multiply 2 times 4 before
performing the subtraction operation because the multiplication sign is of a higher order
than the subtraction sign. The answer is negative 4.
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 6 intersect, so the cell reference, or cell address, is
G6. 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
two bowl amounts together to calculate how much we've spent on bowls.
To do this using cell references, we'll select G6, type an addition sign, and then select
G7. 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 doesn't.
You see, with the way the formula is currently written, Excel will divide G7 by D6 since
division 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 in F2 and multiplies it by the number in C2.
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 in four 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 F2 times C2,you'll see the new relative references.
So here in G3, the formula reads, the value in one cell to the left times the value that
appears in four cells to the left or F3 times C3.
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 precede 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 by the value
in C2. If you want to create an absolute reference,
we'll just add our dollar signs before the C and before the 2.
And press Enter. And we can see that the amounts in G2 and
H2 are the same even though H2 uses an absolute reference.
So, when I fill this formula in to 3 additional cells, I can see that the numbers don't match
up with what appears in G3, 4, and 5.This formula takes the number in F3 and multiplies
it by the number in C2. The C2 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.