Errors

Location: http://www.mvps.org/dmcritchie/excel/errors.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

For some background information, please see HELP (F1) for ISERROR.

ISERR: Value refers to any error value except #N/A.
ISERROR: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

######

The ##### indicates that the number value for the cell as formatted is too wide for the cell. Try widening the cell to see what actually is there.

Take a look in HELP (F1) at the Index tab
#
What does the error ##### mean?

Select the cell, Format, cells, custom
what exactly is the format.
and what exactly is the value you see on the formula bar.

If you enter some large number (above 2,958,465) representing a date above 12/31/9999 or a negative number into a cell formatted as date you will get ###### and you still will have the error no matter how wide you make the column.
12/31/2010 would be 40,543. The 1900 date system is the default. The 1904 date system is default used on MacIntoshes and does accept negative dates.

Things to try:
Make the cell wider, you may need to turn off protection first.. Change the zoom to 100%.

# errors

HELP provides a lot of information on # errors, please read your Excel help.

The error #####
The error #VALUE!
The error #DIV/0!
The error #NAME?
The error #N/A
The error #REF!
The error #NUM!
The error #NULL!
Troubleshoot formulas and error values
Troubleshoot formatting numbers, dates, and times

#DIV/0!

	A	B	C	D
14	3	0	#DIV/0!	=A14/B14
15	3	0		=IF(B15=0,"",A15/B15)
16	3	0		=IF(ISERROR(A16/B16),"",A16/B16)

#NAME! error

The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a formula. (see HELP for more information).
Analysis ToolPak functions unexpectedly return #NAME error value in Excel 2003 and in Excel 2002.
after including ToolPak addins: Force Formula Updating by Using Keyboard Shortcut You can also press CTRL+ALT+SHIFT+F9 to force all formulas to update.

#VALUE! error

There are several things that you should check that are explained in HELP. One common error is an incorrect or mistyped Function name, or User Defined Function (UDF) name. Among common missing function names are those in the Analysis ToolPak:

   ACCRINT        DEC2BIN     HEX2OCT          ISEVEN          SERIESSUM
   ACCRINTM       DEC2HEX     IMABS            ISODD           SQRTPI
   BESSELI        DEC2OCT     IMAGINARY        LCM             TBILLEQ
   BESSELJ        DELTA       IMARGUMENT       MDURATION       TBILLPRICE
   BESSELK        DISC        IMCONJUGATE      MROUND          TBILLYIELD
   BESSELY        DOLLARDE    IMCOS            MULTINOMIAL     WEEKNUM
   BIN2DEC        DOLLARFR    IMDIV            NETWORKDAYS     WORKDAY
   BIN2HEX        DURATION    IMEXP            NOMINAL         XIRR
   BIN2OCT        EDATE       IMLN             OCT2BIN         XNPV
   COMPLEX        EFFECT      IMLOG10          OCT2DEC         YEARFRAC
   CONVERT        EOMONTH     IMLOG2           OCT2HEX         YIELD
   COUPDAYBS      ERF         IMPOWER          ODDFPRICE       YIELDDISC
   COUPDAYS       ERFC        IMPRODUCT        ODDFYIELD       YIELDMAT
   COUPDAYSNC     FACTDOUBLE  IMREAL           ODDLPRICE
   COUPNCD        FVSCHEDULE  IMSIN            ODDLYIELD
   COUPNUM        GCD         IMSQRT           PRICE
   COUPPCD        GESTEP      IMSUB            PRICEDISC
   CUMIPMT        HEX2BIN     IMSUM            PRICEMAT
   CUMPRINC       HEX2DEC     INTRATE          RECEIVED

Common Errors related to Options settings

Cannot find file'C:\Folder Name\....XLS' (or its components). Make sure the path and filename are correct and that all required libraries are available.
-- Options; General [tab]; uncheck the box for Ignore other applications

Related Examples

(placeholder)

Related Information on and off site

Language, other than US English
Toolbars and menus can be be messed up when upgrading to a never version of OS or Excel where the personal.xls takes on the full pathname for instance in your toolbars. Macro to fix
VBA in Excel

You are one of many distinguished visitors who have visited my site here or in a previous location since created on June 10, 2000. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments