Correcting Common Formula Errors

Sometimes, when you enter a formula, Excel displays a value that begins with a pound sign (#). This is a signal that the formula is returning an error value. You have to correct the formula (or correct a cell that the formula references) to get rid of the error display.

Tip

If the entire cell is filled with pound sign characters, this means that the column isn’t wide enough to display the value. You can either widen the column or change the number format of the cell.


In some cases, Excel won’t even let you enter an erroneous formula. For example, the following formula is missing the closing parenthesis:

=A1*(B1+C2

If you attempt to enter this formula, Excel informs you that you have unmatched parentheses, and it proposes a correction. Often, the proposed correction is accurate, but you can’t count on it.

Table 15-3 lists the types of error values that may appear in a cell that has a formula. Formulas may return an error value if a cell to which they refer has an error value. This is known as the ripple effect—a single error value can make its way into lots of other cells that contain formulas that depend on that one cell.

Table 15-3. Excel Error Values
Error ValueExplanation
#DIV/0!The formula is trying to divide by zero. This also occurs when the formula attempts to divide by what’s in a cell that is empty (that is, by nothing).
#NAME?The formula uses a name that Excel doesn’t recognize. This can happen if you delete a name that’s used in the formula or if you have unmatched quotes when using text.
#N/AThe formula is referring (directly or indirectly) to a cell that uses the NA function to signal that data is not available. Some functions (for example, VLOOKUP) can also return #N/A.
#NULL!The formula uses an intersection of two ranges that don’t intersect. (This concept is described later in the chapter.)
#NUM!A problem with a value exists; for example, you specified a negative number where a positive number is expected.
#REF!The formula refers to a cell that isn’t valid. This can happen if the cell has been deleted from the worksheet.
#VALUE!The formula includes an argument or operand of the wrong type. An operand is a value or cell reference that a formula uses to calculate a result.

Handling circular references

When you’re entering formulas, you may occasionally see a Circular Reference Warning message, shown in Figure 15-14, indicating that the formula you just entered will result in a circular reference. A circular reference occurs when a formula refers to its own value—either directly or indirectly. For example, you create a circular reference if you enter =A1+A2+A3 into cell A3 because the formula in cell A3 refers to cell A3. Every time the formula in A3 is calculated, it must be calculated again because A3 has changed. The calculation could go on forever.

Figure 15-14. If you see this warning, you know that the formula you entered will result in a circular reference.

[View full size image]

Intentional Circular References

You can sometimes use a circular reference to your advantage. For example, suppose a company has a policy of contributing 5 percent of its net profit to charity. The contribution itself, however, is considered an expense—and is therefore subtracted from the net profit figure. This produces a circular reference.

The Contributions cell contains the following formula:

=5%*Net_Profit

The Net Profit cell contains the following formula:

=Gross_Income-Expenses-Contributions

These formulas produce a resolvable circular reference. If the Enable Iterative Calculation setting is on, Excel keeps calculating until the Contributions value is, indeed, 5 percent of Net Profit. In other words, the result becomes increasingly accurate until it converges on the final solution.


When you get the circular reference message after entering a formula, Excel gives you two options:

  • Click OK, and Excel displays a Help screen that tells you more about circular references.

  • Click Cancel to enter the formula as is.

Regardless of which option you choose, Excel displays a message in the left side of the status bar to remind you that a circular reference exists.

Warning

Excel won’t tell you about a circular reference if the Enable Interactive Calculation setting is in effect. You can check this setting in the Formulas section of the Excel Options dialog box. (To display this dialog box, select Office Button Excel Options.) If Enable Interactive Calculation is turned on, Excel performs the circular calculation exactly the number of times specified in the Maximum Iterations field (or until the value changes by less than 0.001 or whatever value is in the Maximum Change field). In a few situations, you may use a circular reference intentionally. In these cases, the Enable Interactive Calculation setting must be on. However, it’s best to keep this setting turned off so you’re warned of circular references. Usually a circular reference indicates an error that you must correct.


Usually, a circular reference is quite obvious—easy to identify and correct. But when a circular reference is indirect—as when a formula refers to another formula that refers to yet another formula that refers back to the original formula—it may require a bit of detective work to get to the problem.

Specifying when formulas are calculated

You’ve probably noticed that Excel calculates the formulas in your worksheet immediately. If you change any cells that the formula uses, Excel displays the formula’s new result with no effort on your part. All this happens when Excel’s Calculation mode is set to Automatic. In Automatic Calculation mode (which is the default mode), Excel follows these rules when it calculates your worksheet:

  • When you make a change—enter or edit data or formulas, for example—Excel calculates immediately those formulas that depend on new or edited data.

  • If Excel is in the middle of a lengthy calculation, it temporarily suspends the calculation when you need to perform other worksheet tasks; it resumes calculating when you’re finished with your other worksheet tasks.

  • Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D11, Excel calculates cell D11 before calculating D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you’ll find that processing can slow to a snail’s pace while Excel does its thing. In such a case, set Excel’s calculation mode to Manual—which you can do by choosing Formulas Calculation Calculation Options Manual (see Figure 15-15).

Figure 15-15. You can control when Excel calculates formulas.

[View full size image]

Tip

If your worksheet uses any data tables, you may want to select the option labeled Automatically Except For Data Tables. Large data tables calculate notoriously slowly. Note: A data table is not the same as a table created by choosing Insert Tables Table.


When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can use the following shortcut keys to recalculate the formulas:

  • F9: Calculates the formulas in all open workbooks.

  • Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren’t calculated.

  • Ctrl+Alt+F9: Forces a complete recalculation of all formulas.

Note

Excel’s Calculation mode isn’t specific to a particular worksheet. When you change the Calculation mode, it affects all open workbooks, not just the active workbook.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset