添加时出现有趣的(四舍五入?)错误 [英] Funny (rounding?) errors when adding

问题描述

一列有数字(总是用2个小数，某些是计算出来的，但所有的乘法和除法都舍入为2个小数)，另一列是累加的.累积列的公式为 =<单元上方> + <左侧单元格.

One column has numbers (always with 2 decimals, some are computed but all multiplications and divisions rounded to 2 decimals), the other is cumulative. The cumulative column has formula =<above cell>+<left cell>.

在累积"列中，结果为58.78，在第一列中的下一个数字为-58.78.由于零的格式与正数或负数的格式不同，所以我发现有问题.将格式更改为几位小数，数字显示为:

In the cumulative column the result is 58.78, the next number in the first column is -58.78. Because of different formatting for zero than for positive or negative numbers, I spotted something was wrong. Changing the format to several decimals, the numbers appear as:

                                    £58.780000000000000000000000000000
-£58.780000000000000000000000000000  £0.000000000000007105427357601000

非零零大约为2 ^(-47).再次在相同情况下的数字是:

The non-zero zero is about 2^(-47). Another time the numbers in the same situation are:

                                    £50.520000000000000000000000000000
-£50.520000000000000000000000000000 -£0.000000000000007105427357601000

那怎么会发生?

此外，如果我将累积"列中的单元格更改为实际数字58.78，结果突然变为零.

Also, if I change the cell in cumulative column into the actual number 58.78, the result suddenly becomes zero.

推荐答案

Google表格使用双精度浮点算术，该算术会创建此类工件一个>.此格式的相对精度为2 ^(-53)，因此对于2 ^ 6 = 64左右的多个大小，我们期望2 ^(-47)截断误差.

Google Sheets uses double precision floating point arithmetics, which creates such artifacts. The relative precision of this format is 2^(-53), so for a number of size around 2^6 = 64 we expect 2^(-47) truncation error.

如果某些电子表格用户发现"58.78"实际上不是58.78，他们将感到担心，因为该数字不接受这种浮点格式的确切表示.因此，电子表格隐藏了事实，四舍五入以显示数字，并在要求输入更多数字时打印伪造的零.58.78之后的那些零是假的.

Some spreadsheet users would be worried if they found out that "58.78" is actually not 58.78, because this number does not admit an exact representation in this floating point format. So the spreadsheet is hiding the truth, rounding the number for display and printing fake zeros when asked for more digits. Those zeros after 58.78 are fake.

当您减去两个看似相同但不是—的数字时，真相大白.因为它们是以不同的方式计算的，例如一个通过求和获得，而另一个通过直接输入获得.将减法的结果四舍五入将是一个很大的谎言:这不再是很小的相对误差的问题，2 ^(-47)和0之间的差可能是一个重要的值.因此，幕后机制的意外揭示.

The truth comes to light when you subtract two numbers that appear to be identical but are not — because they were computed in different ways, e.g. one obtained as a sum while the other by direct input. Rounding the result of subtraction to zero would be too much of a lie: this is no longer a matter of a small relative error, the difference between 2^(-47) and 0 may be an important one. Hence the unexpected reveal of the mechanics behind the scenes.

另请参阅:为什么Google Spreadsheets表示零不等于零?

这篇关于添加时出现有趣的(四舍五入?)错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！