Fixing common errors in formulas

If a formula results in ERR or eSuite spreadsheet does not accept your formula and you see an error message, examine the formula for obvious errors such as extra spaces, missing operators or missing parentheses. Check that you specified range addresses correctly; for example B5 or A1..C7. Check for the errors described below.



When a formula results in ERR

What happened Try this
You used zero as a denominator. Don't divide by zero.
You used a range name that was not associated with a range. For example, you entered +Sales+B6+25, and Sales is not a range name in the current worksheet. Associate the range name with an address. For more information, see Naming a range.
You moved data into the first or last cell of a range that is referred to in a formula. Re-create the overwritten data in a new location and then edit the formula to refer to the new location.
You moved data into the first or last cell of a named range referred to in the formula. Re-create the range name, and then edit the formula to refer to the range name.


Other problems

What happened Try this
Your formula looks like a date; for example, 11/30, or 30-Aug (if Aug is a range name). Enclose the formula in parentheses or start it with +. For example (11/30) or +30-Aug.
You entered a formula that begins with a range name, and it didn't evaluate. Enclose the formula in parentheses or start it with + or -.
You entered an address one way, for example A1..B6, and the address changed to a range name. Don't worry. Range ddresses adjust to show what's relevant. For example, if the address is the same as a named range, the address changes to the range name.

ERR can ripple through formulas. Any formula that refers to a cell that contains ERR results in ERR, and any other formula that depends on that formula also results in ERR. When you correct the formula that contains ERR, the results of dependent formulas also become correct.



See also
More power with formulas
More power with @functions
When and how formulas recalculate
Controlling how a formula refers to data
Editing a formula
Entering a range address in a formula
Entering a range name in a formula
Copying a formula
Moving a formula