Order of Operations

When the programmers developed Excel to do math for us, they programmed Excel to always perform the math using very strict order of operations. Excel will always calculate anything in Parenthesis ( ) first. Always. Then it will calculate any Exponents (^). Then, from left to right, Excel will calculate Multiplication (*) and Division (/). And last, Excel will calculate, again from left to right, Addition (+) and Subtraction (-).

That’s the ‘trick’ to getting those Facebook Memes about solving math problems correctly. (see left)

PEMDAS is a mathematical acronym that reminds us of the order of operations used with complex calculations. Understanding this order is critical if you’re going to use more than one type of calculation in your formula.

In the examples below, Excel will come up with very different results if you neglect the Order of Operations.

Reading left to right, you create the equation this way, subtracting the Birth Date from the Retirement Date, and then dividing by 365.25 (the number of days in a year). You’d get the correct result if you subtracted, and then you divided. But that’s not what you told Excel to do.

In the example above, you neglected to communicate with Excel to subtract the Birth Date from the Retirement Date First (by  using parenthesis). So Excel did the division first (Retirement Date divided by 365.25 ), and then subtracted that result from the Birth Date, resulting in the very wrong result.

The correct Order of Operations is below:

How to remember the correct Order of Operations?

I re-teach PEMDAS every time I teach Excel. I teach it in a basic essentials class and I teach it for the advanced class. It’s that important. When you’re creating your Formulas and/or Functions in Excel, if you neglect to communicate with Excel the order in which you want the calculation preformed, then Excel is going to always default back and use mathematical Order of Operations.

Learn more about absolute vs relative references and grouping dates in PivotTables, or Contact us directly and ask about personalized training in Excel. .