Article ID: 224183
Article Last Modified on 6/23/2005
APPLIES TO
- Microsoft Excel 2000 Standard Edition
This article was previously published under Q224183
SYMPTOMS
When you use certain functions, you may receive a different value depending on the version of Microsoft Excel that you are using.
CAUSE
This behavior 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 2000.
This behavior also occurs when you create a workbook in Microsoft Excel 2000 and save it in the Microsoft Excel 95/5.0 format. When the workbook is opened in Microsoft Excel 95, or earlier, these functions return a different value.
MORE INFORMATION
The method used 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:
| Basis | Day count basis |
|---|---|
| 0 or omitted | US (NASD)30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Because these functions were improved, they return different values than in Microsoft Excel 7.0 and earlier. Additionaly, if a workbook that was created in Microsoft Excel 5.0 or 7.0 is opened in Microsoft Excel 2000, the results from Microsoft Excel 5.0 or 7.0 may still be displayed. Even if the workbook is saved in Microsoft Excel 2000, closed and then reopened in Microsoft Excel 2000, the original values may still persist. Also, a recalculation on the workbook by pressing F9 may not resolve the issue. Microsoft Excel will not recalculate to show the correct value until either the formula is edited, the source cells change, or the entire workbook is recalculated.
To fully recalculate all formulas use one of the following methods:
- Use the Calculate Full toolbar button to recalculate all cells in the active workbook. To use this button, you must first add it to a toolbar. When you click this button, Excel 2000 completely recalculates the active workbook, including all dependent and independent formulas.
- Press CTRL+ALT+F9. When you use this key combination, Microsoft Excel completely recalculates all cells in all open workbooks.
- Create and run a Microsoft Visual Basic for Application level Sub procedure to recalculate all cells in all open workbooks.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following sample procedure will recalculate all values in the Excel 2000 workbook each time it is opened. Note that this procedure recalculates the workbook only once each time it is opened.
Private Sub Workbook_Open()
Application.CalculateFull
End Sub
NOTE: Do not attempt to use a Worksheet_Activate or a Worksheet_Calculate procedure to recalculate the values. If you do this, a recursive process begins, and Microsoft Excel performs very slowly. For example, do not use the following procedures:
Private Sub Worksheet_Activate()
Application.CalculateFull
End Sub
-or-
Private Sub Worksheet_Calculate()
Application.CalculateFull
End Sub
The following steps demonstrate the issue with the YIELD function.
Type the following data in a new workbook in Microsoft Excel for Windows 95, 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.
- Save the workbook as Test.xls and close Microsoft Excel.
- Start Microsoft Excel 2000 and open the Test.xls workbook. Press CTRL+ALT+F9 to recalculate the workbook. The value in cell A8 is now 0.257726542.
REFERENCES
For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:
212536 OFF2000: How to Run Sample Code from Knowledge Base Articles
For additional information about Excel recalculation, please see the following article(s) in the Microsoft Knowledge Base:
Additional query words: XL2000 calc recalc calculation recalculation migration
Keywords: kbprb KB224183
