1

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.

Pr0no
  • 3,910
  • 21
  • 74
  • 121
  • This sounds like a bug to me, it would probably be best to report it. – Dugnom May 13 '22 at 13:34
  • 1
    read about floating point representation... floating points can lead to unexpected results sometimes, but this is not a bug - instead its a result of how floating points are represented internally. – Moritz Wolff May 13 '22 at 13:50
  • But how do I fix this? Like, I have conditional formatting in the rest of my sheet that colors cells based on whether the value is `0` or not, and this now obviously messes up. – Pr0no May 13 '22 at 13:53

1 Answers1

3

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
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, but how do I fix it in my sheet? I have many other formulas and conditional formatting checking for `0` (zero) values and I have many instances where this is now going wrong bc of this issue. I have added another few examples to my shared Sheet, one of which actually is going well (with as many decimals). – Pr0no May 13 '22 at 14:00
  • @Pr0no see your sheet. left you there some solutions – player0 May 13 '22 at 14:07