Tag Archives: Formulas

Excel Formula Problems? Try These Troubleshooting Tips!

Ribbon with FBWhen you work with formulas in Microsoft Excel, there are sometimes some glitches. Excel is good about letting you know when there is an error and even offering some help. The Error Checking feature on the Formulas tab in the Formula Auditing Group will find errors such as Divide by zero errors or errors in cell references (#VALUE!) where a cell referenced in the formula does not contain a number.

Also in the Formula Auditing group are the Trace Precedents, Trace Dependents, and Remove Arrows buttons. The Trace Precedents button shows arrows that indicate which cells affect the value of the currently selected cell. The Trace Dependents button show arrows that indicate which cells are affected by the value of the currently selected cell. The Remove Arrows button removes the arrows drawn by the Trace Precedents and Trace Dependent buttons. These buttons are very helpful when determining incorrect cell references in formulas.

However, with especially long formulas, it is sometimes necessary to examine small parts of the formula at a time to figure out the problem. You may use your mouse pointer to look at only a certain part of a formula and press F9 to evaluate just that section. Press Esc to go back and keep searching. The Evaluate Formula button on the Formula Auditing group will also allow you to examine small parts of a long formula.

Another common problem in Excel formulas is a circular reference. A circular reference in an Excel formula is one that depends on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you are building the formula itself. For example, suppose that cell B10 is active when you build this formula,  =A10+B10.   As soon as you enter this formula in cell B10 (assuming the program is in Automatic recalculation mode), Excel displays an Alert dialog box, stating that it cannot calculate the formula due to the circular reference.

Do not forget, too, that by clicking the down arrow at the right end of the formula bar you can expand the size of the formula bar to include more than one line. Use these tips to become an expert in troubleshooting Excel formula problems—something that we all encounter when using Excel’s powerful calculation features!

____________________________________________________________________________________________________
For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.
This entry was posted in Apps, Excel 2013, Microsoft Office 2007/2010 Tips, Microsoft Office 2013, Office Productivity, Productivity Tips and tagged , , , , on by .
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.

This entry was posted in Microsoft Office 2007/2010 Tips, Office Productivity, Productivity Tips, Uncategorized and tagged , , , on by .
Formula AutoComplete in Excel

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.

This entry was posted in Microsoft Office 2007/2010 Tips, Uncategorized and tagged , , , on by .