Learn about Excel's basic "grammar" in equations and never struggle with a formula again. Mastering the Order of Operations, as represented by the mnemonic "Please My Dear Aunt Sally," can help you get accurate results in your Excel calculations, whether you're working on an invoice, a commission split, or any other financial computation.
Key Insights
- Excel formulas follow a specific "grammar" or Order of Operations, represented by the mnemonic "Please My Dear Aunt Sally."
- 'P' stands for parentheses, any operation within parentheses is done first in a formula.
- 'M' stands for multiplication, 'D' for division, 'A' for addition, and 'S' for subtraction. These operations are executed in this order if there are no parentheses.
- Using parentheses can alter the Order of Operations, allowing for flexibility in calculations. For example, to perform addition before multiplication, parentheses can be used to specify this order.
- Understanding and applying the Order of Operations is crucial in getting accurate results in Excel calculations, such as splitting commissions, calculating total sales, and more.
- Breaking down a complex equation into simpler individual computations can help identify mistakes and make it easier to maintain and use Excel worksheets.
PEMDAS in Excel
-
Parentheses
Always evaluated first; wrap subexpressions you want prioritized.
-
Exponents (^)
Powers and roots come second.
-
Multiply & Divide
Evaluated left to right after exponents.
-
Add & Subtract
Last in order, also left to right.
Working with Order of Operations
While you probably know how to do a lot of common calculations – formulas – in your head or on paper, knowing how to instruct Excel to give you the answers you seek is an important skill.
First, let’s look at the basic “grammar” of Excel formulas, so you can build your own in the future without difficulty.
You may remember, as I do, from grade school – the phrase “Please My Dear Aunt Sally.” This stood for P, M, D, A, S, which is meant to remind us of what’s known as the Order of Operations.
What does that mean? Well, formulas are done left to right, and the steps, or operations, in a formula are then performed in the following order:
P is for parentheses, and anything in parentheses is done first. This is important, because without putting anything in parentheses, you’re at the mercy of My Dear Aunt Sally – and by that, I mean…
M is for multiplication, and if there are no parentheses, that’s done first.
Then D for division,
Then A for addition,
And finally, S for subtraction.
If you need anything to be done out of that order – addition to be done before multiplication, for example – then parentheses are your way of making that clear to Excel.
Here on my Demo worksheet, using a simple example, the formula,
=C3+C4*C5
Will, without parentheses, give you a result of 1150. The process? Due to the rules of the Order of Operations, the multiplication is done first – C4 times C5 – and then C3 is added to that result.
With parentheses, for the same numbers in column D, the answer is 250, and that’s correct. Thanks to the parentheses, the two items are added together first and the result of that is then multiplied by the commission percentage. That’s the right formula and the right answer.
In a second demo using both division and subtraction, let’s see it done with and without parentheses.
Here, we have 2 sales reps splitting a commission – so we have to figure out the commission due and then halve it, so that each person gets 50% of the commission, and then we recheck the total commission to make sure it’s the same total being paid out.
Without the parentheses, we get hugely inflated commissions per person, and the same wrong answer for the total commission. Order of Operations, un-altered by the use of parentheses, means that C12 is multiplied by C13, and then that result is divided by 2. Then, we go back and add C11 to that result.
Adding the parentheses, as shown in Column D, gives us the correct 50/50 split for the 2 sales reps, because we’re adding up the two items, multiplying that result by the commission percentage, and then we’re dividing that by 2. In rows 15 and 16, parentheses give us the correct total commission paid as well.
NOTE that the division – dividing the total by 2 – works even without parentheses – because division is always done after multiplication. So keep My Dear Aunt Sally in mind, even if you’ve said Please with parentheses first.
Now let’s do some simple calculations to complete an invoice.
Switching to my Invoice worksheet, we can calculate the total for each line item, and then total the line items using the SUM function, created by clicking the AUTOSUM button.
After that, we’ll calculate the sales tax, the processing fee, and calculate the total due after a discount – a discount on the original invoice Items Total, not including taxes and fees.
By breaking out the individual calculations – the tax, the fee, the discount – we can do a simpler final formula, and we can also spot issues such as incorrect percentages. Any mistakes will leap out visually if they’re calculated separately, rather than buried within a more complicated final formula. This makes it easier for you to edit and maintain your worksheets and for others to use them.
All thanks to My Dear Aunt Sally and remembering to say Please.
