BODMAS Applied to Microsoft® Excel Formulae
Tips for anyone lacking confidence in creating Excel formulae
Last updated on 2020-05-26 by David Wallis.
Microsoft Excel became available on the Mac in 1985 and on the PC in 1987. I’ve presented Excel courses since 1989 to over 2,000 people working for more than 200 businesses ranging in size from SMEs to multi-nationals.
Scores of people attending courses have said to me words to the effect “I’m no good at maths”. I bet I’ll hear that sentiment expressed this year just as frequently as ever. That is why, if any group says it would be of benefit to it, I start each course with a short maths refresher.
Hence this piece. If you lack confidence in your mathematical ability, then I hope it helps you with your use of Excel.
“I’m No Good at Maths”
My thought on hearing that claim is that the person making it is nothing of the sort. I can only guess at where they get that unfortunate notion into their head.
Amongst all the people I have taught Excel who told me of their lack of ability in maths I cannot recall one who could not be shaken from that belief.
Now, I must qualify what we mean by “maths” in the context of these notes and using Excel in the office. We are not talking about finding solutions to the time dependent Schrödinger equation: we are talking about oranges and apples; about pounds, shillings and pence; about quantities and unit prices; about lengths and breadths.
Something like this:
You have six oranges and nine apples to share out between the three girls and two boys in your group so that each kid gets the same number of pieces of fruit. How many pieces of fruit does each kid get?
Is any one of the kids going to be short-changed by your handout? I doubt it.
To work out the share, you either placed the fruit on the table and shuffled it into five equally-sized groups; or you instinctively did some arithmetic: three girls and two boys is five kids; six oranges and nine apples is 15 pieces of fruit; divide 15 by five makes three pieces of fruit for each kid.
No uncertainty over the arithmetic there then; so why any doubt over your ability to do the maths?
I suspect that doubt arises when you come to translate the question of the share out of fruit as expressed in conversational English into a mathematical expression to be put to Excel.
Back to the fruit share-out. Suppose you are avoiding doing the arithmetic mentally. You prepare to put the arithmetic to Excel by expressing the problem in symbols instead of words. The problem looks like 6 + 9 ÷ 3 + 2.
You pop this expression as a formula into a cell in an Excel spreadsheet — =6+9/3+2 — and the result is?
Here, I believe, is where any anxiety you have over doing the maths arises. You are not confident in your understanding of why Excel disagrees with you that each kid should get three pieces of fruit.
In my opinion, this lack of confidence is not due to any innate lack of ability on your part to do the maths: it’s due to the gap in your knowledge of the basic rules of arithmetic.
What you need is BODMAS!
BODMAS is an acronym for Brackets Order Division Multiplication Addition Subtraction:
BODMAS is the order in which the elements of an arithmetic expression are evaluated. Anything within brackets must be work out first and so on down the BODMAS list.
As does any calculator, phone or computer, Excel adheres to the BODMAS rule of arithmetic.
Excel and BODMAS
Back to oranges and apples again: six oranges and nine apples to be shared between three girls and two boys so that each kid gets the same number of pieces of fruit.
In evaluating 6 + 9 ÷ 3 + 2 Excel does the division first (it’s ahead of adddition in the BODMAS scheme), effectively reducing the expression to 6 + 3 + 2, before completing the calculation by addition to get the result of 11.
With BODMAS as our guide, using brackets, we can instruct Excel to add the six and nine together, and the three and two together, before doing the division.
We get Excel to work out (6 + 9) ÷ (3 + 2):
Ordinarily in your use of Excel you would not embed the expression in a single cell as shown above. You would have cells to hold the data and a cell for the formula.
In a similar fashion, you would construct your formula according to BODMAS:
More About Excel and Brackets
Sometimes there is more than one way to apply brackets to achieve the correct result using Excel. Take the calculation of Emma’s expense allowance for her use last year of her own car for business.
Emma drove a total of 7,000 miles in the year, accompanied for 1,500 of those by a passenger. Emma is allowed 45p for each mile driven and 5p for each mile in which a passenger is on board.
Before our appreciation of BODMAS we might have tried this:
Now we know that in the absence of brackets, what Excel has done is work out 7000 × 45 first (to give 315000p); then 1500 × 5 ÷ 100 (to give £75); finally, adding the two to yield that naff result.
To put things right we need to decide at what point we are going to convert pence to pounds. We could do this as we go along without recourse to any brackets:
Or as a final conversion, this time including brackets:
MJW informs me that she was taught BODMAS in primary school and in secondary school until it mutated into BIDMAS when indices became a main topic in maths.
The index of a number, also referred to as the power, tells us how many times a number is to be multiplied by itself. For example, the index 3 in 2³ means 2 × 2 × 2.
Index also covers roots:
√ — the square root also written as 1/2
∛ — the cube root also written as 1/3
∜ — the fourth root also written as 1/4
and so on.
The root of a number is a second number that, when it is multiplied by itself a certain number of times, makes the first number.
Thus the square root of 25 is 5 because 5 × 5 = 25.
The cube root of 27 is 3 because 3 × 3 × 3 = 27.
And the fourth root of 16 is 2 because 2 × 2 × 2 × 2 = 16.
If your life is too short to waste time fretting over an Excel formula that isn’t working as you want it to, I have a routine to suggest:
- Turn away from your spreadsheet
- Make sure you’re clear in your mind what the formula has to do. By that I mean tell yourself about it; even scribble a note
- Consider that in doing completing #2 you kave taken positive action
- Take a break from the spreadsheet: don’t give it another thought — answer some emails, complete some other task, have a coffee, go for a stroll, take a nap.
- Return to the formula and try out any fresh approaches that now may have come to mind.
What I’m suggesting here is that you enlist your subconscious in the pursuit of your formula.
If your formula is proving ellusive, you may need to follow the routine more than once. You may need to leave your subconscious more time to incubate the problem.
A problem I had took two weeks to solve. It was a tough one. I’m grateful to my subconscious for it sparing me from wasting those two weeks glued to my computer fretting about failing to find a solution.
Your subconscious may not deliver a solution at a time that’s altogether convenient. Mine popped me the solution to my two-week-old tough problem whilst I was concentrating on getting in the correct lane for the slip road onto the M4 east-bound at juncion 8/9.
Excel and BIDMAS
In this section I intend to include illustrations of how to use Excel for calculations involving indices.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website, keeping it free of advertising.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.