Errors in Formulas

It's not uncommon to enter a formula only to find that the formula returns an error. Table 2-4 lists the types of error values that may appear in a cell that has a formula.

Table 2-4: EXCEL ERROR VALUES
Open table as spreadsheet

Error Value	Explanation
#DIV/0!	The formula attempts to divide by zero (an operation not allowed on this planet). This also occurs when the formula attempts to divide by an empty cell.
#NAME?	The formula uses a name that Excel doesn't recognize. This can happen if you delete a name used in the formula or if you misspell a function.
#N/A	The formula refers (directly or indirectly) to a cell that uses the NA function to signal unavailable data. This error also occurs if a lookup function does not find a match.
#NULL!	The formula uses an intersection of two ranges that don't intersect. (I describe this concept later in this chapter.)
#NUM!	A problem occurs with a value; for example, you specify a negative number where a positive number is expected.
#REF!	The formula refers to an invalid cell. This happens if the cell has been deleted from the worksheet.
#VALUE!	The formula includes an argument or operand of the wrong type. An operand refers to a value or cell reference that a formula uses to calculate a result.

Formulas may return an error value if a cell that they refer to has an error value. This 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 that cell.

Note 

If the entire cell fills with hash marks (#########), this usually 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. The cell will also fill with hash marks if it contains a formula that returns an invalid date or time.

Depending on your settings, formulas that return an error may display a Smart Icon. You can click this Smart Icon to get more information about the error or to trace the calculation steps that led to the error. Refer to Chapter 21 for more information about this feature.