DMW logo
tel 01732 833085
Tonbridge · Kent · UK

BODMAS Applied to Microsoft® Excel Formulae

Tips for anyone lacking confidence in creating Excel formulae

Last updated on 2021-02-23 by David Wallis.


Preface

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.

Of all the people I have taught Excel who told me of their lack of ability in maths, I can recall only one whom I could not shake 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’re talking about oranges and apples; about pounds, shillings and pence; about quantities and unit prices; about lengths and breadths.

Something like this:

Fruits of BODMAS


Where to Start

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?

I bet your handouts won’t short-changed any kid in your group.

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.

You’re thinking “number of pieces of fruit divided by number of kids”. That is:

six plus nine divided by three plus two

Now you want to use Excel to calculate the share out for you. You pop this expression as a formula into a cell in an Excel spreadsheet — =6+9/3+2.

And what does Excel advise you the share out to be?

Excel and BODMAS

👀❓❓❗❗👿❓❗😡💥

💥 Here, I believe, is where any anxiety you have over doing the maths arises. You’re not confident in your understanding of why Excel disagrees with your assessment 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

BODMAS is an acronym for Brackets Order Division Multiplication Addition Subtraction:

Excel and BODMAS

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.

Excel adheres to the BODMAS rule of arithmetic. Yet you have to be careful about how you explain to Excel exactly what it is you want it to calculate.


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 addition 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, you 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):

Excel and BODMAS

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:

BODMAS Excel Formula


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 mistakenly have tried this:

Incorrect Mileage Calc

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:

Mileage Calc

Or as a final conversion, this time including brackets:

Alternative Mileage Calc


And So to BIDMAS

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 (the I in BIDMAS) of a number, also referred to as the power of a number, tells us how many times a number is to be multiplied by itself.

For example, the index 3 in 23 means 2 × 2 × 2.

34 means 3 × 3 × 3 × 3.

Index also covers roots:
√ — the square root, as in √25, also written as 251/2
∛ — the cube root, as in ∛27, also written as 271/3
∜ — the fourth root, as in ∜16,also written as 161/4
and so on.

The root of a first number is a second number that, when it’s multiplied by itself a given number of times, makes the first number.

Thus the square root of the number 25, i.e. √25, is 5, because 5 × 5 = 25.

The cube root of 27, i.e. ∛27, is 3 because 3 × 3 × 3 = 27.

∜16 is 2 because 2 × 2 × 2 × 2 = 16.

So why “square root”? Why not call it “second root”?

And why “cube root”? Why not call it “third root”?

Why not, indeed. Those alternatives would introduce consistency; but, hey, we’re captives of the history of mathematics.


Mental Preparation

If your life is too short to waste time fretting over an Excel formula that isn’t working as you want it to, then I have a routine to recommend:

  1. Make sure you’re clear in your mind what your formula has to do. By that I mean tell yourself about it; even scribble a note
  2. Turn away from your spreadsheet. Consider that in completing step #1 you’ve taken positive action
  3. 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 power nap
  4. Return to the formula and try out any fresh approaches that now may have come to mind
  5. If none of these work, then ask for help, search the web. Or, if time is not pressing for a result, then repeat the cycle.

What I’m suggesting here is that you enlist your subconscious in the pursuit of your formula.

“Think of your subconscious as the back-office of your brain, dealing with all the deep tasks, while your conscious mind cooks dinner, navigates through traffic, chooses which Netflix show to watch, and holds a conversation. Ever notice all the green cars after you buy a green car, or you take out the trash and a great idea pops in your head from nowhere? This is your subconscious hard at work in the background.”

How to Engage Your Subconscious Mind to Solve Your Toughest Problems

August Birch, 2018-09-07.

If your formula is proving elusive, you may need to follow the routine more than once — to leave your subconscious time to incubate the problem.

“She [Professor Sarah Gilbert] likes to go for walks when thinking through problems—‘It helps to activate the brain,’ she says.”

The race to save the world

The Sunday Times Magazine, February 21 2021

Your subconscious may not deliver a solution at a time that’s convenient. Mine popped me the solution to a by then two-week-old problem whilst I was concentrating on getting in the correct lane for the slip road onto the M4 east-bound at junction 8/9.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“Bodmas…Bidmas?”

Never heard of them!