Microsoft KB Archive/130573

{|
 * width="100%"|

XL5: ATP Functions Evaluate to #REF in MS Excel 4.0 File

 * }

-

The information in this article applies to:


 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows, version 7.0

-

SYMPTOMS
In Microsoft Excel versions 5.0 and later, if you open a Microsoft Excel 4.0 sheet that contains formulas that use the 4.0 Analysis functions, the functions may be preceded with the entire path to the analysis functions as in the following examples.

Windows

  C:\APPS\OFFICE\EXCEL... Macintosh

  Macintosh HD:Applications:Microsoft Office:... These formulas frequently return the #REF error value.

WORKAROUND
To work around this situation, use either of the following methods.

Method 1
Use the following steps to open the MS Excel 4.0 Analysis Functions add-in:


 * 1) Close the Microsoft Excel 4.0 worksheet without saving any changes.
 * 2) From the Tools menu, choose Add-Ins.
 * 3) In the Add-Ins dialog box, click to select the MS Excel 4.0 Analysis Functions check box.
 * 4) After this add-in is loaded, reopen the Microsoft Excel 4.0 file. The formula should resemble the following example ='Analysis Functions'!  and will be evaluated correctly. NOTE: If you add the Microsoft Excel 4.0 Analysis functions while the sheet is open, a #value error value is returned in the cell.

Method 2
Use either of the following procedures to update the links to the 4.0 add- ins:

In Microsoft Excel version 5.0 or later, install the Update Add-in Links add-in. This add-in adds the MS Excel 4.0 Add-in Links command to the Tools menu. Use this command to update links to the Microsoft Excel 4.0 add-ins.

For more information about this add-in, use the following steps in Microsoft Excel version 5.0:


 * 1) On the Help menu, click Contents.
 * 2) Click Reference Information, and then click Microsoft Excel Macro Functions Contents.
 * 3) To find a description of the add-in, click Changing Links To Microsoft Excel Version 4.0 Add-ins.

-or-

If you don't want to use the add-in, use the following steps to update the links manually:


 * 1) On the Tools menu, choose Add-Ins.
 * 2) In the Add-Ins dialog box, click to select the MS Excel 4.0 Analysis Functions check box.
 * 3) Locate the cell where the path is in the formula, and copy the entire pathing information for the Excel 4.0 add-in function to a blank cell.
 * 4) On the Edit menu, click Replace, and then fill in the copied path in the Find What dialog box.

Note that this dialog box does not support the Clipboard.
 * 1) Leave the Replace With box blank and click Replace All.

The formula will be redirected to the Analysis ToolPak for Excel 5.0.

MORE INFORMATION
The following Analysis ToolPak functions will be affected by this behavior:

  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     WORKDAY BIN2DEC        DOLLARFR     IMDIV            NETWORKDAYS     XIRR BIN2HEX        DURATION     IMEXP            NOMINAL         XNPV BIN2OCT        EDATE        IMLN             OCT2BIN         YEARFRAC COMPLEX        EFFECT       IMLOG10          OCT2DEC         YIELD CONVERT        EOMONTH      IMLOG2           OCT2HEX         YIELDDISC COUPDAYBS      ERF          IMPOWER          ODDFPRICE       YIELDMAT COUPDAYS       ERFC         IMPRODUCT        ODDFYIELD COUPDAYSNC     FACTDOUBLE   IMREAL           ODDLPRICE COUPNCD        FVSCHEDULE   IMSIN            ODDLYIELD COUPNUM        GCD          IMSQRT           PRICE COUPPCD        GESTEP       IMSUB            PRICEDISC CUMIPMT        HEX2BIN      IMSUM            PRICEMAT CUMPRINC       HEX2DEC      INTRATE          RECEIVED