Understanding the order of operations in calculations in Excel (BODMAS)

Calculations in Excel follow the mathematical rules known as the order of operations.

Knowing what this order is and how they are applied to calculations can help avoid errors or accidentally calculating incorrect amounts.

Order of Operations

The order of operations is the sequence that mathematical operations should be calculated.

One useful way to remember the order is the acronym BODMAS:

• Brackets

• Orders (powers and roots, such as squares and square roots)

• Division

• Multiplication

• Addition

• Subtraction

  
  

This means that any calculations inside brackets are completed first, followed by orders (like powers and roots), then division, then multiplication, then addition and then finally subtraction.

This can be difficult to understand if reading the formula from left to right, especially if the operators are not in order.

If you had the formula:

=10+2*3

Reading from left to right and ignoring BODMAS it would look like the correct answer would be 36.

This would happen if you calculated in the following steps:

• Addition: 10 + 2 = 12

• Multiplication: 12 * 3 =36

Calculating this way is incorrect, and when done in Excel the correct answer is actually 16, not 36.

Application of BODMAS in Excel

The logic Excel has used to calculate 10+2*3 is:

• Multiplication: 2 * 3 = 6

• Addition: 10 + 6 = 16

This is because Excel has applied BODMAS so the multiplication is done before the addition.

If you wanted the formula to return 36 brackets would be needed.

Using brackets to control calculation order in Excel

Brackets are the first letter in BODMAS which means any calculations in brackets are always done first.

The original formula could be written with brackets in the following way:

=(10+2)*3

When calculating this way Excel will return 36.

As brackets are before multiplication in BODMAS the order the calculation has been done in now is:

• Brackets: 10 + 2 = 12

• Multiplication: 12 * 3 = 36

Dealing with Multiple Brackets

If there are multiple brackets in a calculation Excel will still calculate everything in each set of brackets before applying the remaining BODMAS order.

The following formula contains addition and subtraction in brackets which are then multiplied.

=(6+2)*(5-3)

Excel returns a value of 16 for this calculation.

The order used to calculate this was:

• Brackets: 6 + 2 = 8

• Brackets: 5 – 3 = 2

• Multiplication: 8 * 2 = 16 

Even though in BODMAS multiplication is before addition and subtraction, because the addition and subtraction have been included in brackets they were calculated first before being multiplied by each other.

Conclusion

Formulas in Excel are calculated using BODMAS, which determines the order calculations are applied within a formula.

If you want to control the calculation order and calculate different parts of a formula first then you can use brackets to control this.