Deciphering Formula Errors in Excel 2000

Under certain circumstances, even the best formulas can appear to have freaked out after you get them in your worksheet. You can tell right away that a formula's gone haywire because instead of the nice calculated value you expected to see in the cell, you get a strange, incomprehensible message in all uppercase letters beginning with the number sign (#) and ending with an exclamation point (!) or, in one case, a question mark (?). This weirdness is known, in the parlance of spreadsheets, as an error value. Its purpose is to let you know that some element — either in the formula itself or in a cell referred to by the formula — is preventing Excel from returning the anticipated calculated value.

The worst thing about error values is that they can contaminate other formulas in the worksheet. If a formula returns an error value to a cell and a second formula in another cell refers to the value calculated by the first formula, the second formula returns the same error value and so on down the line.

After an error value shows up in a cell, you have to discover what caused the error and edit the formula in the worksheet. Table 1 lists some error values that you may run into in a worksheet and explains the most common causes.

Table 1: Error Values That You Can Encounter from Faulty Formulas

What Shows Up in the Cell	What's Going On Here?
#DIV/0!	Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no according to our math.
#NAME?	Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.
#NULL!	Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.
#NUM!	Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.
#REF!	Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.
#VALUE!	Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.