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:
- Of / Order
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.