Arithmetic Rules not to be trifled with (Source Image: FreeImages.com by Ear Candy)
Arithmatic Rules are not to be trifled with

Do you still use a calculator to calculate your expenses sheet rather than a spreadsheet like Excel? When you use a calculator to add, subtract, divide and multiply a set of numbers it all goes according to plan and if you don’t make any mistakes then the answer is correct. But when you try to do the same calculation in Excel in one formula, in one cell, you may find you get the wrong answer due to having not used the rules for arithmetic operations in Excel.

There is a hierarchy in calculation operators.

B () Brackets Level 1
O ^ Exponential 2
D / Division 3
M * Multiplication 3
A + Addition 4
S Subtraction 4

When you try to put together a number of different operators in one formula you need to adhere to the hierarchy above, because excel will not calculate the formula in the order you type it but rather the ‘BODMAS’ order as above.

Example1

= 60*5+98/3-7

If calculated using a calculator the answer would be 125.66

The reason this is the case is that the calculator executes the calculation at every stage in the order that you enter it.

If you were to enter this formula into Excel, or indeed any spreadsheet application, the answer would be 325.66 which is wrong.

Excel will perform the calculation in this order. =60*5/3+98-7 thus swapping the addition part with the division as the division is a higher level than the addition.

To get the correct answer you need to place brackets around the parts you want to calculate first with lower operators before you calculate the higher ones further down the formula.

e.g. =(60*5+98)/3-7

Example 2

=9+78-52*5

By calculator 175

By excel -173

So for Excel to work correctly type this = (9+78-52)*5

If you don’t get on with remembering where to place brackets you can always split the formula into several cells. bodmas1

LEAVE A REPLY

Please enter your comment!
Please enter your name here