Excel: Descriptions of Error Values Returned by Functions

ID: Q45758

The information in this article applies to:

SUMMARY

The following table summarizes the definitions of possible error values returned by a formula in Microsoft Excel: 


   Error     Explanation
   -----     -----------

   #DIV/0!   Formula is trying to divide by a zero value or a blank
             cell.

   #N/A      Formula does not have a valid value for argument(s)
             passed.

   #NAME?    Formula contains text that is neither a valid function
             nor a defined name on the active worksheet.

   #NULL!    Refers to intersection of two areas that don't intersect.

   #NUM!     Value is too large, too small, imaginary, or not found.

   #REF!     Formula contains a reference that is not valid.

   #VALUE!   Formula contains an argument of the wrong type.


MORE INFORMATION

The following are more detailed descriptions of the possible error values returned by Microsoft Excel:

#DIV/0!

The #DIV/0! error value means that a formula is trying to divide by zero. This error occurs if the divisor is a blank cell, or if it contains a zero value. For example, the following formulas return #DIV/0!: 


   =25/0
   =50/A2  (where A2 is blank)


#N/A

#N/A stands for "No value is available." This value is returned when you try to enter an array into a range larger than its dimensions. It also is returned if an inappropriate lookup_value is passed to the HLOOKUP, VLOOKUP, LOOKUP, or MATCH functions. For example, the following formulas return #N/A! when they are COMMAND+ENTERed into A1:A4: 


   ={1;2;3}
   =LOOKUP(5,{10;20;30;40})


#NAME?

#NAME? is returned when a formula contains text that Microsoft Excel does not recognize as a function and is not defined as a name. This error commonly occurs when there is a syntax error in a function name, or when a reference is made to a name that has not yet been defined on the worksheet. For example, the following functions return #NAME?: 


   =Averag(A1:A25)
   =SUM(range) (where "range" is not defined on the active worksheet)


#NULL!

This value results when an intersection of two ranges that don't intersect is specified. For example, the following returns #NULL!: 


   =A1:A10 B5:E5


#NUM!

#NUM! indicates a problem with the number returned by the function. This error occurs if the number returned is too large for the system or if it is an imaginary number. It also is returned if a solution cannot be found in a given number of iterations for functions such as IRR or RATE. For example, the following functions return #NUM!: 


   =9^999
   =SQRT(-1)


#REF!

#REF! occurs when a cell that is not valid is referred to. This error occurs in the following circumstances:

  1. If the cell a formula refers to has been deleted


  2. If the formula contains a complex link, or a link using relative references, to a closed document


  3. Any time the argument is not a valid reference


For example, the following formulas all return #REF!: 


   =INDEX(A1:A10,15)
   =B5                  (and then delete B5)
   =ClosedWS!$A$1*5     (where "ClosedWS" is a closed worksheet)
   =ClosedWS!A7         (where "ClosedWS" is a closed worksheet)


#VALUE!

#VALUE! indicates that the wrong type of value has been passed as an argument to the function. This error occurs if text is entered where a number or a Boolean value is expected, and the text cannot be translated into the correct type. For example, the following returns #VALUE!: 


   ="text"+1
   =name*10             (where "name" is defined as "Bill" on the
                        worksheet)


REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 144-149

"Microsoft Excel User's Guide," version 3.0, pages 121-125

"Microsoft Excel Reference," version 2.2, pages 235-240

Additional query words: 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 15, 1999