Excel Formulas and BODMAS

January 22nd, 2019

General

Lynsey


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?Formulas and Bodmas in Excel

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.

=(A1+B1)/C1

=(10+2)/4

=12/4

=3

brackets in Excel

 

 

Without the brackets around the addition, the answer is

=A1+B1/C1

=10+2/4

=10+0.5

=10.05

Brackets in Excel 2

 

 

Where there are more than one set of brackets start with the innermost brackets and work your way out.

=((A1+B1)/C1)^D1

=((8+7)/3)^2

=(15/3)^2

=5^2

=25

Brackets in Excel 3

 

Without all the brackets the calculation is.

=A1+B1/C1^D1

=8+7/3^2

=8+7/9

=8+0.777778

=8.777778

Brackets in Excel 4

 

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 numbersSumming 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.

=A1*B1+C1/D1

=7*5+20/4

=35+20/4

=35+5

=40

Divide and Multiply in Excel

 

 

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.

=A1+B1*C1-D1

=8+7*3-2

=8+21-2

=29-2

=27

Add and Substract in Excel

 

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/).

Excel 2016 Advanced

Excel 2016 Intermediate

Excel 2016 Foundation

Excel 2013 Advanced

Excel 2013 Intermediate

Excel 2013 Foundation

Excel 2010 Advanced

Excel 2010 Intermediate

Excel 2010 Foundation

Excel 2007 Advanced

Excel 2007 Intermediate

Excel 2007 Foundation

Training Calendar View Dates Solab

Sign up to our newsletter