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

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!

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.

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.

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

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.