Tips Excel : Understanding Formula Errors

It’s not always smooth sailing when you’re working with 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:

Image : Error formula excel

• #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”).

Categories: Formula