Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
It's not uncommon to enter a formula and receive an error in return. One possibility is that the formula you entered is the cause of the error. Another possibility is that the formula refers to a cell that has an error value. The latter scenario is known as the ripple effect - a single error value can make its way to lots of other cells that contain formulas that depend on the cell. The tools in the Formulas
Table 3-2 lists the types of error values that may appear in a cell that has a formula.
| Error Value | Explanation |
|---|---|
| #DIV/0! | The formula is trying to divide by 0 (zero) (an operation that's not allowed on this planet). This error also occurs when the formula attempts to divide by a cell that is empty. |
| #N/A | The formula is referring (directly or indirectly) to a cell that uses the NA worksheet function to signal the fact that data is not available. A LOOKUP function that can't locate a value also returns #N/A. |
| # 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. A formula will also display this error if it uses a function defined in an add-in and that add-in is not installed. |
| #NULL! | The formula uses an intersection of two ranges that don't intersect. (This concept is described earlier in the chapter.) |
| #NUM! | There is a problem with a function argument; for example, the SQRT function is attempting to calculate the square root of a negative number. This error also appears if a calculated value is too large or small. Excel does not support non-zero values less than 1E “307 or greater than 1E+308 in absolute value. |
| #REF! | The formula refers to a cell that isn't valid. This can happen if that 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. This error also occurs if your formula uses a custom VBA worksheet function that contains an error. |
| ##### | A cell displays a series of hash marks under two conditions: the column is not wide enough to display the result, or the formula returns a negative date or time value. |