ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

"Please Excuse My Dear Aunt Sally"
Understanding Order of Precedence in Excel Formulas
~~ by Linda Johnson, Linda's Computer Stop

I often get questions from people regarding formulas that are giving incorrect results and, most of the time, it's simply becuz they don't have things in the right order or they have too many or too little parentheses.

"Please Excuse My Dear Aunt Sally" is an old mnemonic device used to remember the order in which the parts of a formula are calculated.

• Please = P = Parentheses
• Excuse = E = Exponents ( ^ power of 2, etc.)
• My = M= Multiplication
• Dear = D = Division
• Aunt = A = Addition
• Sally = S = Subtraction

So, this is the order in which Excel performs the different actions in your formula.  Note that it will multiply BEFORE it adds and it will subtract AFTER it divides.  However, also notice that it performs anything inside parentheses FIRST.  Therefore, if you want Excel to add two numbers BEFORE it multiplies, you simply need to put the addition part of the formula inside parentheses.

Let's look at a simple example.  Let's say you have 10 in cell A1, 20 in cell A2, and 50 in cell A3.

=A1+A2*A3 would give you the result of 1010 becuz Excel would multiply A2 times A3 FIRST (20x50=1000), then add A1 (10) AFTER that and that equals 1010.

But, suppose you want to add A1 and A2 BEFORE you multiply that result by A3?  All you have to do is use parentheses to tell Excel to do the addition FIRST.

=(A1+A2)*A3 would give you 1500 becuz A1 (10) plus A2 (20) equals 30, which is then multiplied by A3 (50) and that equals 1500.

Similarly,  =A3-A2/A1 would give you 48 becuz Excel divides A2 (20) by A1 (10) and gets 2, then subtracts that from A3 (50) AFTER that.

=(A3-A2)/A1 gives you 3 becuz Excel subtracts A2 (20) from A3 (50) FIRST and gets 30, then divides that by A1 (10) and gets 3.

The reason this is so important is most users assume if Excel doesn't give them an error, that their formula is working correctly.  However, none of the above formulas will give you errors, BUT they will give you very different answers.

My recommendation to all of my students is to ALWAYS test your formulas with simple numbers that you can calculate in your head.  Then, and ONLY then, use the formula with your actual numbers.  It is much easier to see if =10+20*50 is giving you the answer you expect, than it is to see that, using =1,475+27.9%*453.  Get my drift?  If the first example gives you what you want, then you can try it on your REAL data.

Also, remember that Excel will perform calculations from left to right, so if you have more than one multiplication inside a formula, it will do the left-most one first.  AND, if you have parentheses inside parentheses, it will calculate the deepest nested parentheses first and work its way outwards.

Hope this helps those of you who have been banging your heads and blaming Excel becuz your formulas are giving you incorrect results.  Most of the time, when this happens, it's becuz you forgot to "Please Excuse My Dear Aunt Sally".

Linda Johnson is a college instructor of all of the Microsoft Office Programs, as well as Adobe PhotoShop and Windows. She also teaches online distance learning classes in Excel, Outlook, PowerPoint, Publisher, and Word at Eclectic Academy. She has worked helpdesk and teaches and lectures at many local businesses and tech schools in her area. Support this newsletter by checking out Linda's eBooks, MS Word MAGIC!, Book I: Fonts, Fun & Formats and Book II: Table Wizardry, How To Get Started As a Software Trainer, and her newest series of MSOffice eBook Tutorials and CD