Why does Excel not round according to 8-byte IEEE 754

by

The article that you linked to is explicit about doing something nonstandard with values near 0:

Example when a value reaches zero
1.In Excel 95 or earlier, enter the following into a new workbook: A1: =1.333+1.225-1.333-1.225

2.Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15.
Instead of displaying 0, Excel 95 displays -2.22044604925031E-16.

Excel 97, however, introduced an optimization that attempts to correct
for this problem. Should an addition or subtraction operation result
in a value at or very close to zero, Excel 97 and later will
compensate for any error introduced as a result of converting an
operand to and from binary.

The unspecified “optimization that attempts to correct for this problem” does mean that caution should be used in using Excel for numerical computations when strict agreement with IEEE 754 is required. Perhaps using VBA (which is unlikely to have this “optimization”?) might be a workaround.

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  3. Read/Parse text file line by line in VBA
  4. How do I find the last column with data?
  5. Excel VBA: Answer gets “stuck”
  6. How to insert a picture into Excel at a specified cell position with VBA
  7. Save multiple sheets to .pdf
  8. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  9. filter out multiple criteria using excel vba
  10. Excel VBA autofilter all but three
  11. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  12. Generating CSV file for Excel, how to have a newline inside a value
  13. What is a subnormal floating point number?
  14. Ignore Duplicates and Create New List of Unique Values in Excel
  15. Excel VBA code to copy a specific string to clipboard
  16. Merge values of column B based on common values on column A
  17. How to import a table from web page (with “div class”) to excel?
  18. Excel VBA – Range.Copy transpose paste
  19. Changing the date format to yyyy-mm-dd
  20. If two cells match, return value from third
  21. Excel Formula to SUMIF date falls in particular month
  22. In Excel, can I use a hyperlink to run vba macro?
  23. Select Newest Record and Create New Table of Unique Values in Excel
  24. Can I export excel data with UTF-8 without BOM?
  25. Are ActiveX Controls Disabled?
  26. Are the bit patterns of NaNs really hardware-dependent?
  27. Denormalized Numbers – IEEE 754 Floating Point
  28. Best way to protect Excel VBA code? [closed]
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment