Troubleshooting formulas

Despite your best efforts, the odd error might appear in your formulas from time to time. Such errors can be mathematical (for example, dividing by zero), or Excel might simply be incapable of interpreting the formula. In the latter case, problems can be caught while you’re entering the formula. For example, if you try to enter a formula that has unbalanced parentheses, Excel doesn’t accept the entry, and it displays an error message. Other errors are more insidious. For example, your formula might appear to be working—that is, it might return a value—but the result might be incorrect because the data is flawed or because your formula has referenced the wrong cell or range.

Whatever the error and whatever the cause, formula woes need to be worked out because you or someone else in your company is likely depending on your models to produce accurate results. Don’t fall into the trap of thinking that your spreadsheets are problem free. A recent University of Hawaii study found that 50% of spreadsheets contained errors that led to “significant miscalculations.” And the more complex the model, the greater the chance that errors can creep in. A KPMG study from a few years ago found that a staggering 90% of spreadsheets used for tax calculations contained errors.

The good news is that fixing formula flaws need not be drudgery. With a bit of know-how and Excel’s top-notch troubleshooting tools, sniffing out and repairing model maladies isn’t hard. This chapter tells you everything you need to know.

Understanding Excel’s error values

When you enter or edit a formula or change one of the formula’s input values, Excel might show an error value as the formula result. Excel has eleven different error values: #CALC!, #DIV/0!, #FIELD!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #SPILL!, #UNKNOWN!, and #VALUE!. The next few sections give you a detailed look at these error values and offer suggestions for dealing with them.

#CALC!

The new #CALC! error appears when Excel’s calculation engine comes across a formula or expression that it doesn’t support. Examples include one of the following:

• An array nested inside another array

• An array that includes one or more range references

• An array that contains no items

#DIV/0!

The #DIV/0! error almost always means that the cell’s formula is trying to divide by zero, a mathematical no-no. The cause is usually a reference to a cell that either is blank or contains the value 0. Check the cell’s precedents (the cells that are directly or indirectly referenced in the formula; see “Auditing a worksheet,” later in this chapter) to look for possible culprits. You’ll also see #DIV/0! if you enter an inappropriate argument in some functions. MOD(), for example, returns #DIV/0! if the second argument is 0.

That Excel treats blank cells as the value 0 can pose problems in a worksheet that requires the user to fill in the data. If your formula requires division by one of the temporarily blank cells, it will show #DIV/0! as the result, possibly confusing the user. You can get around this by telling Excel not to perform the calculation if the cell used as the divisor is 0. This is done with the IF() worksheet function, which I discuss in detail in Chapter 6, “Working with logical and information functions.” (For an even better way to deal with potential formula errors, see “Handling formula errors with IFERROR(),” later in this chapter.) For example, consider the following formula, which uses named cells to calculate gross margin:

=GrossProfit / Sales

To prevent the #DIV/0! error from appearing if the Sales cell is blank (or 0), you’d modify the formula as follows:

=IF(Sales = 0, "", GrossProfit / Sales)

If the value of the Sales cell is 0, the formula returns the empty string; otherwise, it performs the calculation.

#FIELD!

The new #FIELD! error value tells you that your formula is referencing an invalid linked data type. This can happen in three ways:

• Your formula includes a data-type reference to a field that isn’t found in a linked data type. For example, if cell A1 contains a Stocks data type and your formula references A1.[52-week high], you’ll get a #FIELD! error because the 52-week high field doesn’t exist. In this example, you can solve the problem by removing the dash: A1.[52 week high].

• Your formula includes a data-type reference (for example, A1.Price for a Stocks data type) on a cell that doesn’t use a linked data type.

• Your formula includes a data-type reference (for example, A1.Description for a Stocks data type) on a cell that doesn’t have any data for the referenced field.

#N/A

The #N/A error value is short for not available, and it means that the formula couldn’t return a legitimate result. You usually see #N/A when you use an inappropriate argument (or when you omit a required argument) in a function. HLOOKUP() and VLOOKUP(), for example (see Chapter 7, “Working with lookup functions”), return #N/A if the lookup value is smaller than the first value in the lookup range.

To solve the problem, first check the formula’s input cells to see whether any of them are displaying the #N/A error. If so, that’s why your formula is returning the same error; the problem actually lies in the input cell. When you’ve found where the error originates, examine the formula’s operands to look for inappropriate data types. In particular, check the arguments used in each function to ensure that they make sense for the function and that no required arguments are missing.

#NAME?

The #NAME? error appears when Excel doesn’t recognize a name you used in a formula or when it interprets text within the formula as an undefined name. This means that the #NAME? error pops up in a wide variety of circumstances:

• You spelled a range name incorrectly.

• You used a range name that you haven’t yet defined.

• You spelled a function name incorrectly.

• You used a function that’s part of an uninstalled add-in.

• You used a string value without surrounding it with quotation marks.

• You entered a range reference and accidentally omitted the colon.

• You entered a reference to a range on another worksheet and didn’t enclose the sheet name in single quotation marks.

These are mostly syntax errors, so fixing them means double-checking your formula and correcting range name or function name misspellings, or inserting missing quotation marks or colons. Also, be sure to define any range names you use and to install the appropriate add-in modules for functions you use.

Avoiding #NAME? errors when deleting range names

If you’ve used a range name in a formula and then you delete that name, Excel generates the #NAME? error. Wouldn’t it be better if Excel just converted the name to its appropriate cell reference in each formula? Possibly, but there is an advantage to Excel’s seemingly inconvenient approach. By generating an error, Excel enables you to catch range names that you delete by accident. Because Excel leaves the names in the formula, you can recover by redefining the original range name.

Redefining the original range name becomes problematic if you can’t remember the appropriate range coordinates. This is why it’s always a good idea to paste a list of range names and their references into each of your worksheets (select Formulas > Use In Formula > Paste Names > Paste List).

If you don’t need this safety net, you can force Excel to convert deleted range names into their cell references. Here are the steps to follow:

1. Select File > Options to display the Excel Options dialog box.

2. Select Advanced.

3. In the Lotus Compatibility Settings For section, use the list to select the worksheet you want to use.

4. Select the Transition Formula Entry check box.

5. Select OK.

Excel now treats your formula entries the same way Lotus 1-2-3 did way back in days of yore. Specifically, in formulas that use a deleted range name, the name automatically gets converted to its appropriate range reference. As an added bonus, Excel also performs the following automatic conversions:

• If you enter a range reference in a formula, the reference gets converted to a range name (provided that a name exists, of course).

• If you define a name for a range, Excel converts any existing range references into the new name.

#NULL!

Excel displays the #NULL! error in a very specific case: when you use the intersection operator (a space) on two ranges that have no cells in common. For example, because the ranges A1:B2 and C3:D4 have no common cells, the following formula returns the #NULL! error:

=SUM(A1:B2 C3:D4)

Check your range coordinates to ensure that they’re accurate. Also, check to see if one of the ranges has been moved, causing the two ranges in your formula to no longer intersect.

#NUM!

The #NUM! error means there’s a problem with a number in a formula. This almost always means that you entered an invalid argument in a math or trig function. For example, perhaps you entered a negative number as the argument for the SQRT() or LOG() function. Check the formula’s input cells—particularly those that are used as arguments for mathematical functions—to make sure the values are appropriate.

The #NUM! error also appears if you’re using iteration (or a function that uses iteration; see Chapter 2, “Creating advanced formulas”), and Excel can’t calculate a result. There could be no solution to the problem, or you might need to adjust the iteration parameters.

#REF!

The #REF! error means that a formula contains an invalid cell reference, which is usually caused by one of the following actions:

• You deleted a cell to which the formula refers. You need to add the cell back in or adjust the formula reference.

• You cut a cell and then pasted it into a cell used by the formula. You need to undo the cut and then paste the cell elsewhere. (Note that it’s okay to copy a cell and paste it onto a cell used by the formula.)

• Your formula references a nonexistent cell address, such as B0. This can happen if you cut or copy a formula that uses relative references and paste it in such a way that the invalid cell address is created. For example, suppose that your formula references cell B1. If you cut or copy the cell containing the formula and paste it one row higher, the reference to B1 becomes invalid because Excel can’t move the cell reference up one row.

#SPILL!

You see the new #SPILL! error when a dynamic array formula or function that needs to spill data into one or more adjacent cells encounters a non-empty cell in the spill range. (See Chapter 2 to learn more about spill ranges.) You have two ways to handle a #SPILL! error:

• Move the formula that needs the spill range to a new location where the spill range is empty.

• Move the existing data out of the spill range.

In both cases, Excel removes the #SPILL! error and spills the formula result in the new spill range.

#UNKNOWN!

If you see the new #UNKNOWN! error, it means your formula or expression is referencing a data type that isn’t supported in your version of Excel. This most often means that the reference is to a data type that was added to a later version of Excel, so the only way to resolve the error is to upgrade to a version of Excel that supports the data type.

#VALUE!

When Excel generates a #VALUE! error, it means you’ve used an inappropriate argument in a function. This is most often caused by using the wrong data type. For example, you might have entered or referenced a string value instead of a numeric value. Similarly, you might have used a range reference in a function argument that requires a single cell or value. Excel also generates this error if you use a value that’s larger or smaller than Excel can handle. In all these cases, you solve the problem by double-checking your function arguments to find and edit the inappropriate arguments.