Excel’s Order of Operations in Formulas

In Excel the formula =10+20+30+40/4 will render a solution of 70; I think you would agree that 70 is not the average of the four numbers you have added together and then divided by the number of numbers you added (average).  However, =(10+20+30+40)/4 will render a solution of 25, which is, in fact,  the average of the four numbers.  How is one formula different from the other?  It is related to the order in which calculations are performed in formulas. 

Does anyone remember hearing his/her Junior High and/or High School Math teacher mention the phrase, “Please Excuse My Dear Aunt Sally?”  If so, you may also remember that this phrase is important in remembering the order in which formulas are interpreted. The following is the order in which mathematical operators and syntax are applied both in Excel and in general mathematics: 

  1. Parentheses
  2. Exponents
  3. Multiplication
  4. Division
  5. Addition
  6. Subtraction 

In the first example, Excel divided 40 by 4 (division before addition) and added the result to 10+20+30 equaling 70.  However in the second example, the parenthesis forced Excel to first add 10+20+30+40 and then divide the result of the addition by 4 equaling 25–the correct answer.

Do your formulas meet these criteria?  If not, your calculations might be suspect.  So, Please remember to Excuse My Dear Aunt Sally when creating a formula in Excel!

_________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.