Formulas Quick Start Part 4 of 4: Order Of Operations

# Formulas Quick Start Part 4 of 4:

Order Of Operations

**AUTHOR: JASON MORRELL **

**'Order of Operations' is one of the most important but most neglected parts of Excel.**

**Beyond simple formulas, it impacts everything you do in Microsoft Excel, whatever level youâ€™re at.**

**Ignore it at your peril!**

**If you currently use Excel (or you haven't yet started but are thinking about it) then make sure you read through this entire post and understand it fully before moving on to your next task. It will make a huge difference to your progress.**

**FEATURE DOWNLOAD**

Grab the fï»¿reï»¿e checklist:**7 steps to perfect formulas**

## 1. What do Excel and a 17th century maths dude have in common?

The French have produced a LOT of world-renowned mathematicians over the years.

At school you learnt about Descartes and Pascal, but there are many more famous mathematicians including rather a lot of Bernards and Edouardos (search 'French mathematicians' and you'll see what I mean!).

So what does an unknown French mathematician from the 1600s have to do with Excel?

In Excel there is an important rule called the Order of Operations. Itâ€™s actually a set of rules that are followed globally, and if you donâ€™t *know* or donâ€™t *follow* the rules there could be disastrous consequences.

*Like a famous London consulting firm that lost millions because of a simple error in a formula that led to incorrect forecasts and costly recommendations. And probably a very awkward conversation for a certain guy or gal!*

Nobody knows for sure who created the Order of Operations, but it seems they evolved in the 1600s. A French mathematician by the name of DuPlus (probably pronounced â€˜Doo Plooâ€™), is widely considered to be the guy who formulated the rules.

## 2. What is the Order of Operations?

When you have a formula with 3 or more components, the order of operations tell you in which order to process them.

For example, consider this

###### = 2 + 3 * 4

Many people think the answer is 20. After all, if you work left-to-right, 2 + 3 = 5 and 5 * 4 is 20. Stands to reason, right?

However the correct answer is 14.

Why? Because the multiplication is done first, then the addition.

1. Excel **first** calculates the answer to 3 * 4, which is 12.

2. Excel **then** calculates the answer to 2 + 12 which is 14.

Formulas are always calculated in a set order.

This is high-primary school or early-high-school maths. Everybody learnt it. Many people have forgotten it. Donâ€™t worry - you are not alone.

## 3. BEDMAS

The BEDMAS acronym helps you to learn the order. Excel uses symbols to represent mathematical operators. Below, Iâ€™ve explained what BEDMAS stands for and the mathematical symbols that Excel uses.

### B = Brackets - ( )

Anything in brackets is calculated first. You can add brackets to change the natural order.

For example,

###### = 2 + 3 * 4

gives the answer 14, but

###### = (2 + 3) * 4

gives the answer 20

### E = Exponentials (powers) - ^

In layman's terms exponentiation is talking about powers, for example 4^{2}, or 4 squared can be expressed as 4 to the power of 2.

To write a power / exponential in Excel, use the little Chinaman's hat - ^ - which is displayed when you press Shift 6.

For example,

###### = 4 ^ 2

gives the answer 16 (4 x 4), and

###### = 5 ^ 3

gives the answer 125 (5 x 5 x 5)

Unless you're an engineer, exponentials are used much.

### D = Division - '/'

For example:

###### = 100 / 2

###### = A1 / A2

### M = Multiplication - '*'

For example:

###### = 1000 * 1.1

###### = A3 * A4

### A = Addition - '+'

For example:

###### = 1 + 2 + 3

###### = A5 + A6

### S = Subtraction - '-'

For example:

###### = 100 - 50

###### = A7 - A8

It doesnâ€™t actually matter whether you multiply or divide first. Or add or subtract first. But when you have a mixture of addition and multiplication or subtraction and division, then the order is vital.

For example:

###### = 8 + 6 - 2

is the same as

###### = 8 - 2 + 6

Don't risk it. Play it safe. Stick to the list.

So thatâ€™s the Order of Operations. Learn it. Get to know it inside out and upside down. Itâ€™s important.

## 4. Other variations of BEDMAS

I have heard and seen lots of different acronyms used. Hereâ€™s a selection:

As you can see, the last 4 letters in every acronym are the same, even if â€˜Mâ€™ and â€˜D have sometimes been swapped.

â€˜**P**â€™ stands for **Parentheses** (which is a fancy way of saying **Brackets**).

â€˜**I**â€™ stands for **Index** or **Indices** which is another way to say â€˜**Powers**â€™.

â€˜**O**â€™ could mean:

The most creative (and amusing) acronym is the last one - PEMDAS. It stands for Please Excuse My Dear Aunt Sally. I dunno, I guess she broke wind or something!

Test yourself on these

Have a go at these in your head or on a piece of paper, then check your answers in the section below.

###### = 6 - 4 / 2

###### = 2 * 3 ^ 2

###### = 2 + 3 * 5

###### = 2 + (3 * 5)

###### = (2 + 3) * 5

###### = 3 ^ 2 * 3

Okay, hereâ€™s the answers:

1. Answer is **2** â€“> 4 / 2 equals 2, then 6 - 2 equals 4.

2. Answer is **18** â€“> 3 ^ 2 (or 3 squared) equals 9, then 2 * 9 equals 18.

3. Answer is **17** â€“> 3 * 5 equals 15, then 2 + 15 equals 17.

4. Answer is **17** â€“> (3 * 5) equals 15, then 2 + 15 equals 17 - brackets donâ€™t change anything - I was just testing.

5. Answer is **25** â€“> (2 + 3) equals 5, then 5 * 5 equals 25.

6. Answer is **27** â€“> 3 ^ 2 (or 3 squared) equals 9, then 9 * 3 equals 27.

## 5. Watch the video (over the shoulder demo)

## 6. What next?

As you develop more spreadsheets, youâ€™ll need to use the â€˜**Order of Operations**â€™ a lot.

**BEDMAS** is the acronym that will help you remember the order.

Donâ€™t ignore it.

Become familiar with it. You need to know these rules inside out and backwards.

Make it your homework tonight to memorise the 6 letters depicting the order of operations. Thereâ€™s plenty of TV ad breaks. Mute the TV and use the time.

I hope you have enjoyed this mini-series on creating simple formulas in Excel.

**Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! **

**ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.**

Enjoy the rest of your day!

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE