Tag Archives: 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:
- Parentheses
- Exponents
- Multiplication
- Division
- Addition
- 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.
Would you like to enter an Excel function in a cell without typing the entire function name? It is easy to do if you are familiar with the Formula AutoComplete feature in Excel.
To use this feature, click to select the cell in which you wish to enter the function. Type an equal sign and begin to type the function name. As you begin typing the function name, a list of functions appears below the active cell. The functions listed match the letters you have typed. Continue typing until you see the function you want.
Then, double-click the name of the function you want to use. The function and its arguments appear in a ScreenTip below the cell; you can use the ScreenTip as a guide to enter the necessary function arguments.
Give it a try! FormulaAutocomplete is another way to help you get your spreadsheets completed quickly and accurately.
______________________________________________________________________
For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.