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

• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, 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:...

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'!<function name>(<arguments>)
   

   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.
  

      5. Leave the Replace With box blank and click Replace All.
  

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

REFERENCES

For more information in Microsoft Excel version 5.0, see "Installing Add-in Features" in Chapter 37 of the Microsoft Excel "User's Guide," or see Installing or removing an add-in in Help.