98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
The Secret Formula To Creating Correct Calculations In Excel
Tue 24th May 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsMarkel International
Analyst/Developer Christopher Nicholls Access VBA Very good course just pitched at the right level for my requirements Credit Suisse
Event Producer Jennifer Rijkhof PowerPoint Introduction David has been very helpful and has a good knowledge about the subject. It has been a lovely day and I learned a lot. Thank you David. Ps: The food was lovely. East Sussex County Council
Workforce Planning Officer Steve Dumbrell Excel VBA Intro Intermediate Excellent all round - pace and content was about right and delivered with enthusiasm in a friendly and approachable way. |
PUBLICATION GUIDELINES