BODMAS and why it’s important to understand it in Excel

BODMAS

BODMAS is a way of remembering the order of operation that Excel follows when it evaluates values in a formula.

BODMAS stands for:

B – Brackets
O – Order (Indices such as 22 etc.)
D – Division (in an Excel formula we use a /)
M – Multiplication (in an Excel formula we use a *)
A – Addition
S – Subtraction

Example:

What is 4+6/2*3-1?
If you “read” the formula (left to right) you will get 14
If Excel calculates it for you, the answer is 12

These are two different answers, but only one is correct.

In BODMAS, division & multiplication come before addition & subtraction, so what Excel has done is the following…

  1. Divide (there are no brackets or “orders”): 4+6/2*3-1 ⇒ 4+3*3-1
  2. Multiply: 4+3*3-1 ⇒ 4+9-1
  3. Add: 4+9-1 ⇒ 13-1
  4. Subtract: 13-1 ⇒ 12

Of course, if we needed the answer to be 14, then we could have achieved that by using brackets – Excel is still applying BODMAS, but we’re forcing it to calculate in the order that we need, so…

(((4+6)/2)*3)-1 ⇒ 14 – Excel starts with the “innermost” set of brackets & works outwards, so in this case Red, then Blue, then Green

So why is this important?

Why all of this is important is that Excel will calculate this way unless you force it (by using brackets) to do something different… with numbers it might be obvious what has happened, but if you’re building a spreadsheet creating formulas (especially where you do not, yet, have any data) you might enter a formula that “makes sense” to you, but which Excel may treat differently… which is why I always advise people to build spreadsheets with dummy numbers (3, 4, 5, etc or 10, 20, 30) which will test their calculations and, hopefully, easy to check the answers without a calculator!

When testing, I don’t use 0 (zero) in case addition or subtraction is involved (the numbers won’t change), I don’t use 1 for the same reason but with regard to multiplication & division & as 2+2 = 2×2 (2’s can be dangerous!!)