5

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>.

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

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

                                    £50.520000000000000000000000000000
-£50.520000000000000000000000000000 -£0.000000000000007105427357601000

How can that happen?

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

Heimdall
  • 217
  • 2
  • 9

1 Answers1

4

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.

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.

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.

See also: Why does Google Spreadsheets says Zero is not equals Zero?

  • I guess I should just accept that and wrap the cumulative formula into `ROUND(...,2)`. Like the answer in your link suggests, I was considering using a number of pence rather than the number of pounds, and then all numbers stored would be integer (and formatted so that the number 1234 would be displayed as £12.34). I might do it, but it's several interconnected cummulative tables, probably about 150 rows between them. – Heimdall Jan 26 '17 at 10:20
  • I experienced exactly this. One filed was calculated, the other was explicitly given by me, they were -25.000 and 25.000. Summing the was not zero but some value around 10^-12 – karatedog Sep 12 '22 at 14:17