We've already talked about how to create simple formulas that only have one type of operation.
But sometimes you'll have to create a more complex
formula that has more than one type of operation, for example addition and multiplication.
To do this, you'll need to understand how Excel is
calculating the answer.
Excel uses a set of rules called the Order of
Operations, which you might remember from math
class.
The Order of Operations determines which parts of
the formula to calculate first.
The very first thing that you calculate is items
in parentheses.
Then, if you have any exponents you'll calculate those.
Next is multiplication and division, and lastly addition and subtraction.
Excel knows to use the Order of Operations whenever it calculates a complex formula,
and it's important that you remember it
so that you can create formulas that will give you the correct answer.
To remember the order, you can just use the acronym "PEMDAS," or, to make it even easier,
just
remember the phrase, "Please Excuse My Dear Aunt
Sally."
Let's go over each of these in a little more detail.
In this formula, 5+2 is in parentheses, so we'll
calculate that first to get 7, and then we can
just multiply 3 times 7 to get 21.
This formula contains an exponent - 3 to the power
of 2 - and we'll need to calculate that first before doing the addition.
If you have multiplication or division, that will
come next.
Here, we multiply 2*4 to get 8, and 9-8 equals 1.
Now multiplication and division are considered to
be at the same level.
That means if you have both of them in your formula, they're calculated from left to right.
In this example, we'll do the division first and
then the multiplication.
If we wanted the multiplication to happen first,
we could just put it in parentheses, or we could
rearrange the formula so the multiplication was on
the left.
Finally, we have addition and subtraction.
Just like multiplication and division, these are
on the same level, so we calculate them from left
to right.
OK, now let's put them all together.
This formula may look really complicated, but
we're just going to go step-by-step through the
Order of Operations, and you'll see it's actually not that bad.
We'll start with the parentheses.
We'll calculate each of these to get 2 and 3, and
you can see that the formula's already starting to
look a little bit simpler.
Next, we'll look to see if there are any exponents in this formula.
There is one - 2 to the second power - and when we
calculate it it becomes 4.
Next, we'll do the multiplication and division going from left to right.
And now all that's left is addition, and that gives us our answer, which is 8.75,
and that's the same answer that Excel would give
you if you entered in that formula.
It's important to keep these rules in mind when
you're creating a formula.
In this example, I'd like to add these three values, and find out how much the tax will
be.
And let's say the tax rate is 5.5%.
I'll start by typing the equals sign, which is how
you always start a formula.
I'll add D4 plus D5 plus D6, and then I'll multiply it by .055, which is 5.5%.
Now, is this formula going to give us the correct
result?
No, it's not.
Multiplication comes before addition, so Excel will first multiply D6*.055,
and then it will add D4 and D5, and that's actually not what we want.
We want these three values to be added first,
and we can do that by just putting them in parentheses.
Parentheses are often the best way of controlling which parts of the formula are calculated
first.
And when this formula is calculated, the answer is
4 dollars and 65 cents.
So whenever you're doing a complex formula, you'll
need to think about the Order of Operations to
make sure that your formulas give you the correct
answer.