Excel 97-2003 workbooks (.xls) display #N/A! for Analysis Toolpak functions when opened in Excel 2003, Excel 2007 or Excel 2010

Applies to: Microsoft Office Excel 2003Excel 2010Microsoft Office Excel 2007

Symptoms


Consider the following scenario. You have an Excel 97-2003 workbook (.xls) that contains formulas that use functions from the Analysis Tookpak. You opened the workbook in Microsoft Office Excel 2003, Microsoft Office Excel 2007 or Microsoft Excel 2010. The formulas that contained Analysis Toolpak functions in the workbook are replaced with “=#N/A!” however the values display normally until a full recalculation occurs. Once a full recalculation occurs, the values are replaced with #N/A! In addition, formulas that reference those values will also display #N/A!

Cause


The original Excel 97-2003 workbook was opened in Excel 2007, saved under a different file name using the Excel 97-2003 file format, and closed. When you open the new Excel 97-2003 workbook in Excel 2003, Excel 2007 or Excel 2010, the formulas containing Analysis Toolpak functions are replaced with “=#N/A!”. This is a known issue with Excel 2007.

For example, your original workbook was named "Original.xls". When you saved it in Excel 2007, you gave the workbook a new name, "NewCopy.xls". When you open "NewCopy.xls" in Excel 2003, Excel 2007, or Excel 2010, the issue occurs. Opening "Original.xls" in Excel 2007 and saving the file with it's original name does not cause the issue.

Resolution


There are two steps to resolve this issue.

Step 1

You must install the Excel 2007 hotfix described in KB 973932 to prevent future Excel 97- 2003 workbooks from being damaged when they are opened or saved in Excel 2007.

973932  Description of the Excel 2007 hotfix package (Excel-x-none.msp, Xlconv-x-none.msp): August 25, 2009

To ensure you have properly installed the hotfix, perform the following steps.

1.     Locate Excel.exe and Excelcnv.exe in your \Program Files\Microsoft Office\12.0 folder.

2.     Right-click on each file and select Properties.

3.     On the Details tab, check the Fileversion.


If the version of either file is before 12.0.6514.5002, you do not have the hotfix installed.

Step 2

If you have a damaged workbook, you will need to recreate the formulas using the Analysis Toolpak functions. Since the issue occurs when you save the file to a new name, you may be able to go back to the original workbook to obtain the proper formulas.

More Information


This issue only affects formulas that contain Analysis Toolpak functions. The worksheet functions that are loaded as part of the Analysis ToolPak include the following:


   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