An important part of understanding how Excel calculates formulas and functions is understanding BIDMAS. Let’s think back to math class. I remember the teacher talking about BIDMAS and vividly thinking that I’d never need to use it. Attend any Excel course with me and it’ll be the first thing we discuss when we begin the fun of creating formulas.
When anyone starts using Excel for formulas it is common to double-check the answers with a calculator. If the answer is wrong, this will often cause the user to lose faith that Excel is going to consistently produce accurate information. A large portion of these instances I tend to find all comes down to the order in which users enter their formula. Not taking into account what we were taught way back when (for me anyway) and apply those same BIDMAS rules into our formulas in Excel.
BIDMAS stands for Brackets, Indices, Division, Multiplication, Addition and Subtraction. You may also see references to BODMAS and PEMDAS. Each acronym means the same thing however using different terminology.
It represents the order that your formulas will be calculated. Just because you type a formula into Excel from left to right, does not mean that is the order in which Excel will perform the final calculation.
Let’s look at an example:
Let’s say I enter the following formula into Excel:
Reading from left to write this is asking Excel to add 50 and 70, which gives us 120. Next multiply that by 2, which gives us a final answer of 240. But Excel produces an answer of 190. Go ahead and try it yourself!
Now look at the BIDMAS rule, Excel is going to calculate the 70*2 first giving an answer of 140 and then add the 50, which gives us 190.
Ok, so how do we fix that? This is where the brackets part of the acronym comes into play. Anything wrapped in brackets is calculated first, regardless of whether it is addition, subtraction, multiplication or division.
So let’s try this same calculation again.
Voila! Now we get the answer of 240!
So I hope this explains why you may occasionally get an answer from Excel which doesn’t seem right. It’s another reason that we need to make sure that we are accurate in the way we enter a formula. It is rare that Excel gives a wrong answer, more so it’s the user entering the formula incorrectly.
Now that you have a good understanding of BIDMAS and how it will affect your Excel calculations, continue on and read my post introducing you to Functions in Excel, or have some fun using the LOOKUP function or join content from different cells together using Concatenate.