BODMAS Applied to Excel Formulae
Tips for anyone lacking confidence in creating Excel formulae
Last updated on 2017-12-22 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,500 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 Excel; 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 is 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.
Excel adheres to the BODMAS rules.
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, effectively reducing the expression to 6 + 3 + 2, before completing the calculation by addition.
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: