A friend of mine created a spreadsheet to calculate how much it would cost him to change to a new mobile phone provider. The result scared him to death. According to his spreadsheet, based on his expected usage (he’s a low user) it was going to cost him almost £500 per month!
When I examined the spreadsheet I found that adding 4 brackets to the formula was all that was needed to generate a more accurate and realistic figure of £18. The contract provided 120 “free minutes” of talk time and 100 free SMS messages each month.
The original formula in B9, which is incorrect, is =B1-B2*B3+B5-B6*B7
The correct formula in B10 is =(B1-B2)*B3+(B5-B6)*B7
So why the huge difference?
To help answer that, lets look at a question that somebody posted on my FaceBook feed recently…
What is the answer to this calculation: 7+7/7+7*7-7
Hundreds of people had replied and provided various answers, including 56 (incorrect) and 50 (correct).
So why 56? Well, many people calculate the answer by working from left to right…
- 7 plus 7 = 14
- 14 divided by 7 = 2
- 2 plus 7 = 9
- 9 mulitplied by 7 = 63
- 63 – 7 = 56
However, as I said above, the answer is actually 50. The division (7/7) is done before the multiplication (7*7) which is done before the addition (7+) and the subtraction (-7)
So you get…
- 7 divided by 7 = 1
- 7 multiplied by 7 = 49
- 7 plus 1 = 8 (the 1 is the result of 7 divided by 7)
- 8 plus 49 = 57 (the 49 is the result of 7 multiplied by 7)
- 57 – 7 = 50
Back in the late 1980’s, when I first started working with spreadsheets, I was introduced to an acronym known as BODMAS, which had nothing specifically to do with spreadsheets and everything to do with the order of mathematical operations.
BODMAS stands for:
- Brackets
- Of / Order
- Division
- Multiplication
- Additon
- Subtraction
OK, so we know what DMAS stands for but what about the B and O?
The B stands for brackets. If you need part of the calculation to be computed separately (ignoring the DMAS rules), you need to use brackets. In the mobile phone spreadsheet, the brackets are used to indicate that the result of B1-B2 is multiplied by B3. The result of this is added to the result of B7 multiplied by B5-B6
The O..well that depends who you speak to and what websites you read. Some say it stands for “Of”, for example 2 to the power of 3.
So that’s BODMAS.
But there’s more…I’ve also heard it called BOMDAS, although because multiplication and division are ranked equal, it doesn’t matter which is calculated first. Some people say it’s simply that BODMAS is easier to say that BOMDAS! My American colleagues refer to it as PEDMAS or PEMDAS – P stands for Parentheses and E stand for Exponential.
Whatever you call it, the important point to remember is that whenever you need to work out part of the calculation separately, use brackets/parentheses.
Mike,
What’s really scary about this is that BODMAS doesn’t appear to be taught any more in schools!
When I’m running one of my Excel training courses, I always ask whether people have heard of BODMAS & it’s usually the “older generation” that have, & younger generation not… even when I’ve explained what it means & used an example (similar to the one you’ve done), it’s not always familiar to the younger members on the course…
Deep joy!
Cheers
Colin
Was BODMAS ever taught in schools? I certainly don’t remember it. The first time I heard of it was when I started delivering Lotus 123 for DOS training in the late 80’s! It was in the manual that someone had written.
Whenever I mention it these days, I do get people who know that you do the bit in brackets first but that’s about the level of their knowledge!
I don’t recall ever being taught the BODMAS acronym in school – nor as I went through higher education as an engineering student. Though I probably learned the underlying principles of algebraic operator precedence in primary school… (And I think we’re of a similar vintage.)
Can we make it a little more interesting and do it in RPN please? This would work:
7 7 7 / + 7 7 * + 7 –
Sometimes I miss my old HP 11C…
Oh noes – look what you made me download just now (at least this one is currently free!). So much for getting to bed early tonight, yawn…
https://itunes.apple.com/gb/app/11c-scientific-calculator/id530593018?mt=8
I remember BODMAS from somewhere, but not sure if I was actually ever taught it at school.
When I’m training Excel I always ask my learners to think of a calculation like ’10-2*3′ & ask them what they think the answer should be.
They, usually, always answer “24, obviously…”
I then ask them to enter this into Excel so they can all see what Excel makes of it & ask them if they can explain why the Excel answer is 4?
I’m amazed how few people have heard of BODMAS & just seem to accept whatever answers Excel provides them with.
Best Regards,
Jonathan
I know I did not learn BODMAS/PEDMAS in school.
We did learn the order of operation and the opportunity to bypass the rules by using parentheses, but we didn’t have a mnemonic device.
I wasn’t taught either in the Dark Ages when I went to school. Some people around here are taught BEDMAS but I prefer PEMDAS (reversing the M and D) because you can use this memory trigger Please Excuse My Dear Aunt Sally. I only learned that one when I started training.
Bodmas, pedmas, pidmas, bidmas, and bedmas almost forgot badmas where I =inversion and a= accent have you got any others
As an Excel Trainer, and ex Lotus and Quattro Pro Trainer and also as an ex Maths teacher, I am generally appalled by the lack of basic maths knowledge shown by some of the delegates on my courses. There may be a problem of adult literacy in this country but there is a definite problem with adult numeracy. So many flounder over simple percentages let alone more complex calculations. Many delegates appear not to have a grounding in algebra and consequently struggle with spreadsheets. I am firmly of the belief that the standard of maths education in this country, abysmal in many cases, needs to be addressed. Also, I have lost track of the times people have said to me ‘oh I was never any good at maths at school’ or ‘I could never do percentages’ or words to that effect! How are people to use spreadshseets correctly and effectively when the fundamental mathematical skills are weak? Onwards and upwards Colin!
“oh I was never any good at maths” – last week I went into Tesco and asked for 1 Kilo of sliced meat.
The young girl behind the counter said “that’s 100KG”. I said “No that’s 1KG” (she actually said “one hundred kay gee”)
She said “I’m new here and not very good at maths”
Hi
Interesting article on Excel and mathematical conventions. Upfront, I’m a teacher (Science) and I can 100% support that BIDMAS / BODMAS is taught in Year 7/8 in the UK. Whether or not this is retained post school in employment is another issue entirely.
Formulating mathematical expressions correctly in Excel is the bane of my life – not in my personal use, but when a colleague says “look at this” it’s wrong. Invariably brackets are involved somewhere in the error.
This comes to sharp focus when calculating means, A1+B1+C1/3 produces a number that is bigger than any of the data – a fact that is impossible, but because Excel told them, it must be correct.
Thought provoking article.
Glen