Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

h
A function that uses iteration can’t calculate a result. Examples of functions that use

iteration are IRR and RATE.

h
A formula returns a value that is too large or too small. Excel supports values between

–1E–307 and 1E+307.

#REF! errors

The #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the

following situations:

h
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

h
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

h
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

h
You cut a cell (by choosing Home

Cut) and then paste it to a cell that’s

referenced by a formula. The formula will display #REF!.

➜

Clipboard

➜

#VALUE! errors

The #VALUE! error is very common and can occur under the following conditions:

h
An argument for a function is of an incorrect data type or the formula attempts to

perform an operation using incorrect data. For example, a formula that adds a value to a text

string returns the #VALUE! error.

h
A function’s argument is a range when it should be a single value.

h
A custom worksheet function (created using VBA) is not calculated. With some versions

of Excel, inserting or moving a sheet may cause this error. You can press Ctrl+Alt+F9 to

force a recalculation.

h
A custom worksheet function attempts to perform an operation that is not valid. For

example, custom functions cannot modify the Excel environment or make changes to

other cells.

h
You forget to press Ctrl+Shift+Enter when entering an array formula.