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 2020-05-26 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.

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:

Fruits of BODMAS

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?

Excel and BODMAS

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

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.

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):

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 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


Whence 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 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.


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, I have a routine to suggest:

  1. Turn away from your spreadsheet
  2. 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
  3. Consider that in doing completing #2 you kave taken positive action
  4. 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.
  5. 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.

“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 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

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!