Here’s a quick but very useful Excel tip. It is especially helpful when performing calculations on lengthy columns.
Give this tip a try to see if you like this method better than dragging the fill handle down, which is the method used by many who work with Excel daily. It is easy and very fast!
For more information on the Business Office Systems and Support department, contact Angela Nino, Lead Faculty, firstname.lastname@example.org, 972-238-6382.
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:
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, email@example.com 972-238-6215.