Funny (rounding?) errors when adding

Question

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.

Answer 1

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?