Google Sheets SUM() does not return zero, but an extremely small number instead?

Question

I have a Google Sheet with the following values:

 12.4840
 -8.1870
 -0.9630
 -3.3210
  3.4550
  0.3140
  3.3470
 -7.1290

If I SUM() these, the expected result is 0 (zero). But it is not. The value that Google Sheets returns is actually 0.000000000000000888178419700125. This is super weird, as none of the values have more than 4 decimals.

I found this out after debugging for hours because some conditional formatting is supposed to color every cell that has a 0 value, but this (and some other) cells just would not change color accordingly.

I have an example here: Stackoverflow Google Sheet. Can someone please explain me what is going wrong here, and how I can get the SUM() to return true zero? I have tried everything (format input as numbers, as text, force to numbers in sum() formula) but nothing seems to work.

[Update] I have added a few more examples in the Sheet.

Answer 1

actually, this is not a bug and it is pretty common. its called floating point "error" and in a nutshell, it has to do things with how decimal numbers are stored within a google sheets (even excel or any other app)

more details can be found here: https://en.wikipedia.org/wiki/IEEE_754

to counter it you will need to introduce rounding like:

=ROUND(SUM(A1:A))

this is not an ideal solution for all cases so depending on your requirements you may need to use these instead of ROUND:

ROUNDUP
ROUNDDOWN
TRUNC
TEXT