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:
- Close the Microsoft Excel 4.0 worksheet without saving any changes.
- From the Tools menu, choose Add-Ins.
- In the Add-Ins dialog box, click to select the MS Excel 4.0 Analysis Functions check box.
- 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:
- On the Help menu, click Contents.
- Click Reference Information, and then click Microsoft Excel Macro Functions Contents.
- 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:
- On the Tools menu, choose Add-Ins.
- In the Add-Ins dialog box, click to select the MS Excel 4.0 Analysis Functions check box.
- 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.
- 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.
- 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
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.
Additional query words: 5.00a 5.00c tool pack pak
Keywords : kberrmsg
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbprb
Technology :
Last Reviewed: April 12, 2000 |