The Secret Formula To Creating Correct Calculations In Excel

Tue 24th May 2011

Did you know that remembering and understanding a basic maths calculation could save you time and money when using Excel? Find out if you need to go back to the classroom before applying correct formula to your worksheets.

We all know that there is usually a reason why we do things in a certain order: we eat before we brush our teeth; remove our footwear before attempting to peel off our skinny jeans. If we didn't, we would end up forking out financially (perhaps for an unnecessary dentist's bill), or take so much time to carry out a simple task we wouldn't bother to try again (ban the skinnys to a life in the back of the closet along with our other fashion faux pas). And when it comes to using Excel successfully, it's also important to make sure you do everything in the right order, otherwise the consequences could result in wasted time and money.

One rule that can keep us on track so that any calculations we combine in formula are always going to be correct is BODMAS (or BOMDAS, or even BEDMAS depending on which part of the globe you learnt basic maths). These little an acronyms - which most of us have filed in our long-term memory banks during long forgotten algebra classes - are the key to performing correct calculations and they are extremely important when creating formula in Excel. This is because the order that you allocate to any mathematical instruction can result in a correct or incorrect answer. It's no good if you have carefully set up your worksheet to display data from different sources, but can't understand how to calculate the formula correctly. And this is where BODMAS comes into its own.

To demonstrate the right and wrong way to calculate a formula, look at the following example: calculating the sum =5+3/2, we would expect the result to be 4. However Excel produces the result 6.5. Why? Because of BODMAS Excel calculated 3÷2 and then that number was added to 5. To specify that we want Excel to add 5 and 3 and then divide the answer by 2, we enclose it in brackets: =(5+3)/2. In a nutshell, that means that Excel will divide or multiply before it adds or subtracts unless specified by brackets. As you can see if you are hoping to use add and subtract along with multiply and divide in a formula, you need to know how to apply BODMAS.

When trying to solve equations or constructing formulas in Microsoft Excel, you need to ensure that the equation or formula is solved in the correct order; it's not simply a case of working through a calculation from left to right. If the formula or equation is not solved in the correct order, you may find the result you get is not the one you hoped for. Imagine the consequences if this resulted in a miscalculation that cost your organisation money?

BODMAS stands for: Brackets, Of (or Over or Orders), Division, Multiplication, Addition, Subtraction. If you prefer to use E (exponent) instead of Of, then BEDMAS is your winning code. It's the best way to remember the order of operation that Excel follows when it evaluates values in a formula. And here's the order the calculation works:
1. Parts of an equation enclosed in 'Brackets' must be solved first. There are no exceptions to this rule.
2. Any mathematical 'Of' or 'Exponent' as must be solved next. Again, there are no exceptions to this rule. Most computer programs like Microsoft Excel do not have a mathematical 'Of', so you can ignore the 'Of'.
3. Next, the parts of the equation that contain 'Division' and 'Multiplication' are calculated. Where division and multiplication follow one another, then regardless of their order that part of the equation is solved left to right. You will find it very important to get this order correct when doing addition and subtraction.
4. Last, but not least, the parts of the equation that contain 'Addition' and 'Subtraction' should be calculated. As with division and multiplication, where addition and subtraction follow one another, then regardless of their order that part of the equation is solved left to right.

Although in the UK it is more common to use BODMAS (Brackets, Of/Over/Orders, Divide, Multiply, Add, Subtract), BEDMAS (Brackets, Exponents, Divide, Multiply, Add, Subtract) is usual in some other countries including Canada. Other people refer to brackets as indices which makes it BIDMAS; or in the US, where brackets are referred to as parentheses, it is sometimes known as PEMDAS. It really doesn't really matter how you remember it, just so long as you get the order right. This has to be the order of all equations - it might not always be necessary but it is always good practice, so follow it and you will always be sure of creating correct calculations.

You can easily test out the formula in Excel by selecting a blank cell, then hitting = (equals) on the keyboard followed by the equation. Now hit the Enter key on the keyboard or click OK to accept your calculation (formula). Obey these simple rules, and your formulas in Excel will always calculate the correct answer; and, just as importantly, you will also be able check that your formulas are correct.

Author is a freelance copywriter. For more information on excel courses, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1695-secret-formula-creating-correct-calculations-in-excel.html