XL98: Certain Functions Calculate Differently in Early Versions of Excel (223990)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q223990

SYMPTOMS

When you use certain functions, you may receive a different value depending on the version of Microsoft Excel you are using.

CAUSE

This issue occurs when the following conditions are true:
  • You create a workbook in Microsoft Excel for Windows 95, version 7.0 or earlier.

    -and-

  • You use one of the following functions and set the basis parameter to 2:
    YIELD
ODDFYIELD
ODDFPRICE

    -and-

  • You open the workbook in Microsoft Excel 98 Macintosh Edition.
This behavior also occurs when you create a workbook in Microsoft Excel 98 Macintosh Edition and save it in the Microsoft Excel 5.0/95 format. When the workbook is opened in Microsoft Excel 5.0, these functions return a different value when the workbook is recalculated.

MORE INFORMATION

The method that you use to calculate these functions when the basis parameter is set to 2 was changed after Microsoft Excel for Windows 95, version 7.0, to produce more accurate values than in earlier versions of Microsoft Excel. Basis is the type of day count basis used by the function and is defined in the following table:

BasisDay count basis
0 or omittedUS (NASD)30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360

Because these functions were improved, they return different values than in Microsoft Excel 7.0 and earlier. Additionally, if a workbook that was created in Microsoft Excel 5.0 or 7.0 is opened in Microsoft Excel 98, the results from Microsoft Excel 5.0 or 7.0 may still be displayed. Even if you save the workbook in Microsoft Excel 98 and close and reopen it in Microsoft Excel 98, the original values may still be present.

If you recalculate the workbook by pressing COMMAND+=, the issue may not be resolved. Microsoft Excel does not recalculate to show the correct value until you edit the formula, change the source cells, or recalculate the entire workbook.

To fully recalculate all formulas, press COMMAND+SHIFT+F9. When you use this key combination, Excel completely recalculates all cells in all open workbooks.

The following steps demonstrate the issue with the YIELD function.
  1. Enter the following data in a new workbook in Excel version 7.0 or earlier: 
    A1: 2/18/99
A2: 6/16/02
A3: .11875
A4: 71
A5: 100
A6: 1
A7: 2
A8: =YIELD(A1,A2,A3,A4,A5,A6,A7)
    The value in cell A8 is 0.257365886.
  2. Save the workbook as Test and close Excel.
  3. Start Excel 98 and open the Test workbook. Press COMMAND+SHIFT+F9 to recalculate the workbook. The value in cell A8 is now 0.257726542.

REFERENCES

For additional information about Excel recalculation, please see the following articles in the Microsoft Knowledge Base:

223314 XL98: Custom Function May Not Calculate Expected Value

184636 XL98: Using Calculate Method May Not Calculate Certain Formulas

186686 XL98: Custom Function Is Not Recalculated with Intersection

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbprb KB223990
©2002 Microsoft Corporation. All rights reserved.