为什么Excel不符合8字节的IEEE 754 [英] Why does Excel not round according to 8-byte IEEE 754
问题描述
以下表达式计算为C＃中的 false ：
1 + 1 + 0.85）/ 3 <= 0.95
我想它在大多数其他编程语言实现IEEE 754，因为（1 + 1 + 0.85）/ 3 评估为 0.95000000000000007 ，这是更大的比 0.95 。
然而，即使 Excel应该实现IEEE 754的大部分，以下在Excel 2013中评估为TRUE：
=（（1 + 1 + 0.85）/ 3 <= 0.95）
有什么具体原因吗？上面链接的文章没有提到可能导致此行为的任何自定义的Excel实现。请您注意，尽管大多数Excel问题都是在superuser.com上提出的，但是这个问题涉及浮点数算术，这是编程语言中的常见问题。从这个问题的角度来看，Excel是一种编程语言，如C＃或Java。
您链接的文章是明确的做一些非标准值接近0：
值达到零的示例
1.在Excel 95或更早版本中，在新的工作簿中输入以下内容：A1：= 1.333 + 1.225-1.333- 1.225
2.右键单击单元格A1，然后单击格式化单元格。在数字选项卡上，单击类别下的科学。将小数位数设置为15.
而不是显示0，Excel 95显示-2.22044604925031E-16。
然而，Excel 97引入了一个优化，尝试以纠正
这个问题。如果加法或减法运算结果
在非常接近零的值，Excel 97及更高版本将
补偿由于将
操作数转换为二进制文件而导致的任何错误
未指定的尝试更正此问题的优化意味着谨慎使用Excel进行数值计算需要与IEEE 754的严格协议。也许使用VBA（这不太可能有这个优化？）可能是一种解决方法。
The following expression evaluates to false in C#:
(1 + 1 + 0.85) / 3 <= 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.
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)
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?
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.
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.
这篇关于为什么Excel不符合8字节的IEEE 754的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
