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.
The #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the
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:
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:
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:
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!.
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
h You forget to press Ctrl+Shift+Enter when entering an array formula.