7

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.

Improve this question

1 Answer 1

Reset to default
6

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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
Just a note: I don't think Google Sheets uses full double-precision, but something closer to 15 digit precision, which is perhaps used to round the 16-17 raw digits and hide most floating point issues. For example, =1/3 seems to produce 0.333333333333333 which has 1 less '3' than JavaScript and Python do.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.