In our previous article, we discovered how to create the Perfect Formula – Every Time. Now we are going to investigate the rules behind how Excel calculates these results.

OK before we start, here’s a Pop Quiz!

Question:

The formula below,

3 + 2 x 9 =

Answer:

Would the answer be A or B?

A:   45

B:   21

The answer is B!

Those of you who voted B, when you’re done high-fiving your monitor and chair dancing (just go for it, by the way), spare a thought for those who voted A, who are staring at the screen and John McEnroe-ing (“You cannot be serious!”).

Oh but I am very serious!

Don’t feel bad A-voters!  As it turns out this is one of the things that challenges most Excel users.

But do you know Why?

Excel determines the result of all calculation by applying the Universal Rule –

The Order of Precedence.

If you are thinking that phrase sounds familiar – it probably is, in fact, it is likely you learnt all about the Order of Precedence in primary school (Junior) math.  Your teacher may have called it something slightly different, but the principles will be the same.

Let’s familiarise ourselves with the rules.

The Order of Precedence

There are 6 elements to the rules which determines the order in which calculations are performed.  Oh and by the way, if you didn’t work it out from the Pop Quiz, formulas are not resolved by reading left to right and here is why…

B rackets

O f

D ivision

M ultiplication

A ddition

S ubtraction

If we consider our original quiz question:

3 + 2 x 9 =

We must first ask:

1. Are there any brackets? – No.
2. Are there any “Power Of” symbols? – No.
3. Is there Division or Multiplication? – Yes, there is multiplication. So this calculation will be done first.

2 * 9 = 18

4. We now look at the partially calculated formula and repeat the same questions.

3 + 18 =

5. Are there any brackets? – No
6. Are there any “Power Of” symbols? – No
7. Is there Division or Multiplication? – No
8. Is there Addition or Subtraction? – Yes
9. So now add the two figures to come to 21.

Answer = 21

The interesting thing to note is that Division and Multiplication both have the Order value of 3.  This means that when you see Division and Multiplication in the same equation, in the absence of brackets you simply calculate the values from left to right, rather than giving precedence to one before the other.

The same is true for Addition and Subtraction.  They both share the Order value of 4.  Therefore as long as they are the only symbols remaining in the equation they will be calculated from left to right, also.

You must always be mindful of the Order of Precedence when creating formulas in Excel.  Otherwise, you could end up expecting one answer but getting another.

How would you change the original quiz question so that the answer would be 45?

Easy! Use brackets…

(3 + 2) * 9 = 45

Don’t be afraid to experiment and try this out in Excel! It won’t bite, I promise.

Join me next time when we investigate the difference between absolute and relative cell referencing!