Excel Formula Errors - dummies

Excel Formula Errors

It’s not always smooth sailing when you’re working with Excel formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list:

  • #DIV/0!: This error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.

  • #N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.

  • #NAME?: This error value means Excel doesn’t recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.

  • #NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.

  • #NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.

  • #REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).

  • #VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).