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 -10^2 . Ray wondered how Excel coped and we got curious too.
We usually ignore these types of questions that go around the web. They are deliberately ambiguous and no self-respecting mathematician would write an equation that could be misinterpreted.
Some ambiguous equations vs Excel
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.
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 a 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.
PEMDAS = Parentheses, 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.
- Brackets/ Parentheses.
- Ranges – :
- Intersections – spaces
- Unions – ,
- Negation – –
- Percentages – % (aka divide by 100)
- Exponents / Powers – ^
- Multiplication * and division /
- Addition + and subtraction –
- Text operators – &
- Comparison – =, <>, <=, >=
Percentage and Negation ahead of the usual order
Putting Negation and Percentages ahead of others can affect results.
As we’ll see in a moment, 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.
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 (which isn’t recognized by Excel) so we tried entering it into Excel using 1 / 2 or ( 1 / 2 ) and 0.5.
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 () .
