Working with Order of Operations

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.

So 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.