Episode 2 of Lynsey’s “From Excel Zero to Excel Hero” Series – learn more about Excel Functions and … BODMAS!
Excel Formulas and BODMAS
What is BODMAS
In English we read from left to right, but calculations steps are not completed from left to right, they are calculated in the order defined by BODMAS. So, what
does the acronym BODMAS stand for?
Divide and Multiply are interchangeable so we could use BOMDAS. Also Add and Subtract are interchangeable so it could even by BODMSA or BOMDSA but for now we will stick with BODMAS. Let’s find out how this affects your Excel formulas.
Brackets in Excel
Brackets come first. This means that any calculation within a set of round brackets is done first. In the following example the addition happens first, because it is in brackets, then the result is divided by C1.
Without the brackets around the addition, the answer is
Where there are more than one set of brackets start with the innermost brackets and work your way out.
Without all the brackets the calculation is.
Hint for Beginners: Brackets are on the keyboard above 9 and 0. Use SHIFT and 9 or 0.
Orders in Excel (Summing & Cubing Numbers)
This is to the power of or square roots. You may have heard of summing and cubing numbers. Summing is to the power of 2 and cubing is to the power of 3. Five to the power of 2 is the equivalent of multiplying 5 by 5 to equal 25. So, 5 to the power of 5 is the equivalent of 5*5*5*5*5.
In Excel use the caret ^. You’ll find it on the keyboard above the 6. Press SHIFT and 6.
Divide and Multiply in Excel
Where the calculation contains a division and a multiplication you perform those operations from left to right. Take the following example. Start by multiplying 7 by 5 to equal 35 then divide 20 by 4 to equal 5. Finally add 35 and 5 together to equal 40.
In Excel use the * (star or asterisk) to multiply. Use SHIFT and 8 or if your keyboard has a number pad you will see the star key there. To divide use the / (forward slash or oblique). Find it in the bottom right of the keyboard next to the SHIFT key or again you can find it on the number pad.
Add and Subtract in Excel
Add and Subtract rank equally. They happen last in the calculation but where there is an addition and a subtraction you carry out those calculation steps from left to right. In this example the multiplication happens first then the subtraction and finally the addition.
The add symbol in Excel is + which you can find above the equals sign or on the number pad.
The subtract symbol is – which is next to 9 or on the number pad.
BODMAS – something to remember!
BODMAS is something to remember if your calculation does not give the answer expected. Check out next week’s post to find out other common reasons why formulas and functions go wrong.
From Excel Zero to Excel Hero – EXCEL courses in Aberdeen
Solab have been offering businesses IT Application Training since 1994. We consistently receive positive feedback and excellent evaluation from clients and individual delegates. We’re flexible, too – not only in the diversity of our course programme, but in shaping a training solution that precisely matches you and your business requirements. We can host your training at our training school in Aberdeen city centre, at your premises or in the cloud (for course delivery methods and options, please visit: https://www.solab.co.uk/training/delivery-methods/).