XL97: Certain Functions Calculate Differently in Early Versions of Excel

ID: Q221075

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

When you use certain functions, you may receive a different value depending on the version of Microsoft Excel 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 97.

This behavior also occurs when you create a workbook in Microsoft Excel 97 and save it in the Microsoft Excel 95/5.0 format. When you open the workbook in Microsoft Excel 7.0 or earlier these functions return a different value.

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:

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 you create in Microsoft Excel 5.0 or 7.0 is opened in Microsoft Excel 97, the results from Microsoft Excel 5.0 or 7.0 may still be displayed. Even if you save the workbook in Microsoft Excel 97, close and then reopen it in Microsoft Excel 97, the original values may still be present.

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

To fully recalculate all formulas, use one of the following methods:

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The following sample procedure will recalculate all values in the Excel 97 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.SendKeys "^%{F9}"
   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.SendKeys "^%{F9}" 
   End Sub

-or-


   Private Sub Worksheet_Calculate()
       Application.SendKeys "^%{F9}" 
   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 97 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 additional information, please see the following article in the Microsoft Knowledge Base:

Q173707 OFF97: How to Run Sample Code from Knowledge Base Articles

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

Q223312 XL97: Custom Function May Not Calculate Expected Value

Q171339 XL97: Some Values Not Recalculated When Using Multiple Formulas

Q186685 XL97: Custom Function Is Not Recalculated with Intersection

Additional query words: XL5 XL7 XL97 XL2000 calc recalc calculation recalculation migration

Keywords :
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb