An important part of understanding how Excel calculates formulas and functions is understanding BIDMAS.
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 applying 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 that 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:
=50+70*2
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 adding 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.
=(50+70)*2
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.