Office Tip – Control the order in which calculations are performed

When working with calculations in Office applications, i.e. VBA or using formulas in Excel, many mistakes are made when dealing with the order of operation. Generally, Microsoft tries to stay close to the rules of Maths when performing calculations, that is, it evaluates expressions from left to right and uses laws such as multiplication before addition.

For instance, the calculation 4 + 3 * 2 would result in 10 because Microsoft will calculate 3 * 2 first before adding the result to 4. This type of decision making is called operator precedence and it not only applies to Maths but can be extended to any operation that is possible within Microsoft applications. For instance, addition always receives precedence over concatenation (joining items) so in the calculation 4 & 3 + 12 Microsoft will first add 3 and 12 together to receive 15 and then join this with the number 4 to receive 415 overall.

If we are unsure about how a calculation is performed or want to change the behaviour of operator precedence, then we can use brackets around the expression we want to evaluate first. I.e. in the example above we can write (4 & 3) + 12 to force Microsoft to first join 4 and 3 together to receive 43 and then add the number 12 to receive the result 55.24

Share this tip on