Excel’s order of calculation and BODMAS

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel has a slightly different order of calculation to the BODMAS rule taught to us at school.  Those variations are important and can give a result you didn’t expect.

Ray E from Iowa asked about one of those tricky algebra questions that appear on social media and generate heated arguments. Ray wondered how Excel coped and we got curious too.

The questions that go around the web are deliberately ambiguous. No self-respecting mathematician would write an equation that could be misinterpreted.

We compiled some of these examples and dropped them into Excel to see what happened.  Excel did well but there are anomalies or variance (not necessarily errors) marked in brown and we’ll explain below.

image 60 463x296 - Excel’s order of calculation and BODMAS

The original formula text (copied from some online source), Excel’s result and the exact formula used to get that result.

BODMAS and friends 101

For those of us who had math classes decades ago, BODMAS is an memory aid for the order of operations in an equation done by humans.

Brackets

Order (powers and roots)

Division

Multiplication

Addition

Subtraction

Division & Multiplication are the same level of precedence as are Addition & Subtraction.

If there are operators at the same level (e.g. many additions and subtractions) they are done left to right.

Nested parenthesis calculations are done from the innermost brackets first.

BIDMAS and PEMDAS

BIDMAS and PEMDAS are two other mnemonics that use slightly different words.

BIDMAS = Brackets, Indices, Division, Multiplication, Addition, Subtraction.

PEMDASParentheses, Exponents, Division, Multiplication, Addition, Subtraction.

BODMAS in Excel

Excel, for the most part, obeys the BODMAS rule but not completely. There are cases where Excel goes off on its own. Here’s Excel’s order of operation with our highlighting.

  1. Brackets/ Parentheses.
  2. Ranges  – :
  3. Intersections – spaces
  4. Unions – ,
  5. Negation  – –
  6. Percentages – % (aka divide by 100)
  7. Exponents / Powers  – ^
  8. Multiplication * and division /
  9. Addition + and subtraction –
  10. Text operators – &
  11. Comparison  – =, <>, <=, >=

Putting Negation and Percentages ahead of others can affect results.

As we’ll see, negative numbers are treated differently in Excel than your professor or high-school teacher might approve.

Percentages should, strictly speaking, be considered Division but are given higher priority.  The formula  =150*25%  is converted by Excel into  150*0.25 then evaluated.  Giving % a higher priority makes sense but offends some purists.

=-10^2

This is an infamous example of Excel going off from BODMAS by putting negation ahead of Powers/Exponents.

image 42 - Excel’s order of calculation and BODMAS

The correct result for =-10^2 is -100  (10 x 10 then negative) but Excel puts the negation first (-10 x -10 = 100).

It’s an unusual situation but worth keeping in mind.  Adding the brackets =-(10^2) makes the formula clear.

0.5 or half doesn’t equal ½ or 1/2

This is another anomaly we discovered compiling our formula list.  The original formula text used the ½ symbol so we tried entering it into Excel using    1 / 2 , ( 1 / 2 ) and 0.5.

image 61 473x56 - Excel’s order of calculation and BODMAS

As you can see, there are two different results because of the ‘left to right’ order that the multiply and divides are done.

The lesson …

Use brackets (or parentheses if you prefer) whenever you think extra clarity is necessary.

Unlike math teachers, Excel won’t deduct marks for unnecessary extra () .

subs profile e1563205311409 - Excel’s order of calculation and BODMAS
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address