Recently, while I was hunched over a spreadsheet on my laptop at the kitchen table, my 10-year-old daughter asked, “Is that Excel?” It was. “Can I show you something?” she asked. In a blank cell, in a new blank workbook, she typed the following:
When she pressed Return the number 2 appeared in the cell instead of the formula. “It’s good, isn’t it?” she said. It was. She is learning Excel at school, in ICT. I have spent many years of my working life training and coaching people on spreadsheets. There are thousands of people in the UK who have been taught, by me, how to use Excel (and other, mostly forgotten packages) better. I showed her how to use other arithmetic operators (- for a subtraction, * for multiplication and / for division) and left it at that.
Over the weekend, on one of our Saturday morning trips to town, I asked her what the answer to the following formula would be:
one plus two times three.
She said “Nine”, as I would expect her to, at this stage of her education. “You’d think so, wouldn’t you? When we get home we’ll try it in Excel, and it might turn out to be seven instead.”
“BODMAS” I told her, and explained how I had learnt about BODMAS at the age of ten, and had spent much of my adult life teaching other grown-ups about it.
BODMAS tells you the order that calculations happen in a formula. It’s an acronym which stands for:
Order, or Of (as in “to the power of”)
Many years ago I trained a woman from Scotland who had been taught it as BEDMAS, which uses E (for Exponential) instead of O. Either way, in any formula that does not contain brackets Multiplication will always happen before Addition. The formula “One plus two times three” gives the answer seven by calculating as follows: two times three, plus one.
In a spreadsheet the formula is:
If you want it to give you the answer 9 instead of 7 you’ll need some brackets in the formula, like this:
In this case 1+2 happens first (to give 3), and then it’s multiplied by 3, to give the answer 9.
This all brought back memories of my schooldays, aged 10, and the dramatic way in which our Maths teacher introduced the subject. He walked into the class and said, without introduction, “BODMAS”, stretching out the syllables so it sounded more like “BOD … MASS”. He said it two or three more times before telling us what it meant.
On Saturday afternoon I showed my daughter how the formulas above work in Excel, with and without the brackets. She hasn’t been taught BODMAS at school yet but it should be a familiar concept for her whenever it happens, either in Maths or in ICT.
I did explain the “to the power of” part of BODMAS but don’t expect her to remember it right now. Most adults don’t use spreadsheets to calculate powers (2 to the power of 3, for instance) but for the record you use a ^ symbol (Shift + 6 on most keyboards) to do so. Here’s a formula that will calculate (without brackets) “two times three to the power of two”:
What would you expect the answer to be? Will it be “two times three (which is six) to the power of two”, which gives the answer 36? Or will it be “three to the power of two (which is nine) times two”, which gives the answer 18?
It’s 18, thanks to BODMAS, or BEDMAS: “to the power Of”, or Exponential, is calculated before the Multiplication. The simple way to minimize any doubt about how your formulas are calculated is to use Brackets, even if they turn out to be redundant. The following formula will definitely give the answer 7:
And this formula will definitely give the answer 18:
In those last two formulas the brackets are redundant but at least you can see clearly the order in which things will happen. If in doubt, stick some brackets in.