Chapter 19: Making Your Formulas Error-Free
19
The following formula doesn’t return #NULL! but displays the contents of cell B9, which
represents the intersection of the two ranges:
=SUM(B5:B14 A9:F9)You also see a #NULL! error if you accidentally omit an operator in a formula. For example,
this formula is missing the second operator:
= A1+A2 A3#NUM! errors
A formula returns a #NUM! error if any of the following occurs:
■ You pass a nonnumeric argument to a function when a numeric argument is
expected (for example, $1,000 instead of 1000).
■ You pass an invalid argument to a function, such as attempting to calculate the
square root of a negative number. This formula returns #NUM!:
=SQRT(-12)■ (^) A function that uses iteration can’t calculate a result. Examples of functions that
use iteration are IRR and RATE.
■ (^) A formula returns a value that is too large or too small. Excel supports values
between –1E-307 and 1E+307.
#REF! errors
A #REF! error occurs when a formula uses an invalid cell reference. This error can occur in
the following situations:
■ (^) You delete the row column of a cell that is referenced by the formula. For example,
the following formula displays a #REF! error if row 1, column A, or column B is
deleted:
=A1/B1
■ (^) You delete the worksheet of a cell that is referenced by the formula. For example,
the following formula displays a #REF! error if Sheet2 is deleted:
=Sheet2!A1
■ You copy a formula to a location that invalidates the relative cell references. For
example, if you copy the following formula from cell A2 to cell A1, the formula
returns #REF! because it attempts to refer to a nonexistent cell:
=A1-1
■ You cut a cell (choose Home ➪ Clipboard ➪ Cut) and then paste it to a cell that’s
referenced by a formula. The formula will display #REF!.