Let’s talk about the rules of precedence / order of operations which can be remembered by the acronym BODMAS.
Quick History
No, these are not new rules for maths. They are actually a few centuries old (around the 18th and 19th to be specific.
They tell you the order in which a maths calculation should be worked out.
If you’d like a longer explanation:
In mathematics and computer programming, the order of operations (or operator precedence) is a collection of rules that reflect conventions about which procedures to perform first in order to evaluate a given mathematical expression.
Wikipedia, https://en.wikipedia.org/wiki/Order_of_operations
Why is it important
That’s got a simple answer – Excel automatically uses these rules, so that answers that appear in your spreadsheet may not be what you expected.
Now, if you are only using simple calculations like a+b, or c/d then you’re fine.
However, once you start asking Excel to do something more, like adding, dividing and multiplying in the same calculation, then it can make a difference.
What are the rules?
In any calculation we normally read left to right, however following these rules you need to look at the calculation to be able to work it out.
Let’s take a standard example:
2 + 3 * 4 =
What’s the answer?
2 + 3 (= 5) and 5 * 4 = 20
Not with the rules or Excel.
Where does BODMAS come in?
That’s the acronym that we use (especially in the UK) that reminds us of the order:
B = Brackets
O = Over/Order
D = Division
M = Multiplication
A = Addition
S = Subtraction
Let’s go back to our example: 2 + 3 * 4
First question: Are there any brackets?
No, so onto the next option (we kinda ignore the O)
Is there a division? No
Is there a multiplication? Yes
We need to do that first
3 * 4 = 12
Now it’s 2 + 12
As there’s only one thing left – let’s just calculate that:
2 + 12 = 14
And that’s our answer.
Moving forwards, if we want to have the answer 20, we can use BODMAS to perform it in the order we want.
Remember – Brackets, they come first.
As we want the addition 2 + 3 to be calculated – that needs to be inside brackets.
(2 + 3) * 4
In Excel that will equal 20.
Impact
With our simple example, there’s a difference of 6 between the expected answer (20), and the ‘correct mathematical answer (14).
Now it may not seem a lot, but if you’re using Excel for your business – that can be quite a difference.
Now you know all about BODMAS and the rules, you can take them into account, and write formulas in your spreadsheets that will give you the expected result!
What more tips and tricks of your computer applications? Book a Call and let’s chat how I can help.
Related Posts
- Excel Paste Values – Get Exactly What You See, Every Time
If you’ve ever copied something in Excel and ended up with a completely different result than you expected, you’ve probably fallen foul of one of Excel’s little quirks. The good news is that once you understand Excel Paste Values, you’ll know exactly how to handle it – and it only takes a couple of extra… Read more: Excel Paste Values – Get Exactly What You See, Every Time - Excel Sparklines: The Tiny Charts Making a Big Difference
If you’ve ever stared at a column of numbers and thought “I just want to see the pattern” — Excel Sparklines are about to become your new favourite thing. These miniature charts sit inside a single cell, right next to your data, giving you an instant visual snapshot without the faff of building a full… Read more: Excel Sparklines: The Tiny Charts Making a Big Difference - Do You Know All the Excel Flash Fill Tricks?
If you’ve ever exported data and thought “why is everything jumbled together in one column?”, Excel Flash Fill is about to become your new best friend. It’s one of those features that looks impressive but is actually really simple to use — and once you know it, you’ll find yourself reaching for it again and… Read more: Do You Know All the Excel Flash Fill Tricks? - The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity
When most people think about keyboard shortcuts, Microsoft Word typically comes to mind first. However, as an Excel enthusiast, I’m here to tell you that Excel has its own powerful set of keyboard shortcuts that can dramatically improve your productivity. Today, I’ll guide you through the complete A-Z of Excel’s keyboard shortcuts for Windows users.… Read more: The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity - Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour