Excel浮点行为规范 [英] Specification of Excel floating point behaviour

问题描述

尽管Excel使用标准的IEEE754 binary64格式，但它在算术和比较中没有使用相同的规则，例如

Though Excel uses the standard IEEE754 binary64 format, it doesn't use the same rules for arithmetic and comparisons, e.g.

=0.1+0.2=0.3

返回TRUE和其他 确切的行为在任何地方都有记录吗?

Is the exact behaviour documented anywhere?

推荐答案

经过一些实验，我建立了以下行为:

Having done some experiments, I have established the following behaviour:

1. 如果两个值等于15个有效十进制数字(即printf lingo中的"%.14e")，则根据相等运算符(=)等于相等"

1. Two values are "equal" according to the equality operator (=) if they are equal to 15 significant decimal digits (i.e. "%.14e" in printf lingo)

• 小于(<)且小于或等于(<=)被定义与此一致(即，如果在IEEE算术下为true，则"Excel小于"为true，并且值不为"Excel等于").

• Less than (<) and less than or equal (<=) are defined to be consistent with this (i.e. "Excel less than" is true if it is true under IEEE arithmetic, and the values are not "Excel equal").

如果单元格公式中的最终运算符(根据通常的运算符优先级)是-或+，并且结果的大小小于第一个运算符的8 ulps(最后一个单位)参数，然后将结果设置为零.

if the final operator (according to usual operator precedence) in a cell formula is - or +, and the magnitude of the result is less than 8 ulps (units in last place) of the first argument, then the result is set to zero.

• 可以通过将整个表达式用括号括起来来抑制此行为(或等效地，您可以将此类括弧作为运算符来查看).

SUM似乎以与+的链接序列相同的方式起作用(阈值似乎是累积总和的倒数第二个元素的8 ulps)，但是无法通过以下方式抑制截断包装括号.

SUM appears to act in the same way as a chained sequence of + (the threshold appears to be 8 ulps of the second last element of the cumulative sum), but the truncation cannot be suppressed via wrapping parentheses.

请注意，这些值不一致，因为它们的值可能是"Excel相等"，但差异非零，反之亦然.

Note that these are not consistent, in that values may be "Excel equal" but have a non-zero difference, and vice-versa.

因此，如果您想要更接近Excel中的IEEE算术:

Therefore, if you want something closer to IEEE arithmetic in Excel:

• 将所有公式括在最后的括号中

• wrap all formulas in a final parentheses

使用(a-b)<0代替a<b(对于其他布尔运算符也是如此)

use (a-b)<0 instead of a<b (and similarly for other boolean operators)

避免使用SUM

(由于缺少次态和有符号的零，因此仍然不严格)

(this will still not be strict due to lack of subnormals and signed zeros)

这篇关于Excel浮点行为规范的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！