Excel为什么不按照8字节IEEE 754四舍五入 [英] Why does Excel not round according to 8-byte IEEE 754

问题描述

以下表达式在 C# 中的计算结果为 false:

The following expression evaluates to false in C#:

(1 + 1 + 0.85) / 3 <= 0.95

我认为在大多数其他实现 IEEE 754 的编程语言中也是如此，因为 (1 + 1 + 0.85)/3 的计算结果为 0.95000000000000007，后者更大比 0.95.

And I suppose it does so in most other programming languages which implement IEEE 754, since (1 + 1 + 0.85) / 3 evaluates to 0.95000000000000007, which is greater than 0.95.

但是，即使 Excel 也应该实现 IEEE 754 的大部分内容，以下在 Excel 2013 中计算为 TRUE:

However, even though Excel should implement most of IEEE 754 too, the following evaluates to TRUE in Excel 2013:

= ((1 + 1 + 0.85) / 3 <= 0.95)

有什么具体原因吗?上面链接的文章没有提到任何可能导致这种行为的 Excel 自定义实现.你能告诉 Excel 严格按照 IEEE 754 进行舍入吗?

Is there any specific reason for that? The article linked above does not mention any custom implementations of Excel that can lead to this behavior. Can you tell Excel to strictly round according to IEEE 754?

请注意，尽管大多数 Excel 问题都应在 superuser.com 上提出，但此问题涉及浮点运算，这是编程语言中的常见问题.从本题的观点来看，Excel 是一种类似于 C# 或 Java 的编程语言.

Please note that even though most Excel questions should be asked on superuser.com, this question deals with floating-point arithmetic, which is a common problem in programming languages. From the viewpoint of this question's topic, Excel is a programming language like C# or Java.

推荐答案

你的文章链接到 明确表示使用接近 0 的值进行非标准操作:

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

值为零时的示例1.在Excel 95或更早版本中，在新工作簿中输入以下内容:A1:=1.333+1.225-1.333-1.225

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.右键单击单元格 A1，然后单击设置单元格格式.在数字选项卡上，单击类别下的科学.将小数位数设置为 15.Excel 95 不显示 0，而是显示 -2.22044604925031E-16.

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 引入了一项优化，试图纠正对于这个问题.加法或减法运算的结果值为零或非常接近零时，Excel 97 及更高版本将补偿由于转换的结果引入的任何错误二进制操作数.

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.

未指定的试图纠正此问题的优化"确实意味着在需要严格遵守 IEEE 754 时，在使用 Excel 进行数值计算时应谨慎行事.也许使用 VBA(不太可能有这种优化"?)可能是一种解决方法.

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.

这篇关于Excel为什么不按照8字节IEEE 754四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！