You may already have some experience creating simple formulas that have only one type of
operation...
But sometimes you have to create more complex formulas that do have more than one operator;
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 are the items in parentheses.
Then, if you have any exponents, you'll calculate those.
Next is multiplication and division.
And finally, addition and subtraction.
Excel knows to use the Order of Operations whenever it calculates a complex formula.
It's important that you remember it, so you can create formulas that'll give you the correct
answer.
To remember the order, you can just use the acronym "PEMDAS."
Or, to make it even easier, 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.
We'll need to calculate that first... and then we can do the addition.
If you have multiplication or division, that'll come next.
Here, we multiply 2 times 4 to get 8... and 9 minus 8 equals 1.
Now multiplication and division are considered to be on 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'll 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 that it's really not that bad.
We'll start with the parentheses.
We'll calculate each of these to get 2 and 3... and as you can see, the formula's already
starting to look a bit simpler.
Next, we'll look to see if there are any exponents in this formula.
There is one—2 to the second power.
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 Excel would give you, if you entered that formula in your worksheet.
Now I'm going to show you why it's so important that you follow the Order of Operations every
time.
If you don't, your formulas won't give you the correct answer.
In this example, I'd like to add these three values, and find out how much the sales tax
will be.
I'll start by typing the equal sign, which is how you always start a formula.
Then I'll calculate D3... plus D4... plus D5.
Next I'll multiply by .075, which is the tax rate at 7.5%.
Now, this may look like it's going to give us the correct answer, but it's not.
That's because we didn't follow the Order of Operations.
Remember, multiplication comes before addition—so Excel is going to multiply D5 by .075 first...
then it'll add D3 and D4, which isn't what we want.
We want these three values to be added first.
So to do that, we just need to put 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... we can see that the answer is $20.56.
So whenever you're doing a complex formula, just remember to follow the Order of Operations.
Then you'll get the correct answer, every time.