Search code examples
google-sheetsnumbers

Why is Google Sheets calculating extra decimals where it shouldn't?

I am trying to track down a gremlin in a Google Sheet and have exhausted my brain power. I'm hoping someone out here can enlighten me. Google sheets is arbitrarily adding or subtracting tiny amounts whenever it feels like it. In this particular instance, it is subtracting 0.0000000000004.

Let me be clear... I know that I can wrap the calculation in a ROUND(), as a workaround, but I'm trying to figure out what I might be doing wrong to create this artifact to begin with or if this is a Google Sheets bug.

I have isolated the offending sheet. I copied and pasted values only where data was coming from other sheets, then severed all connections to other Sheets. Here is a screenshot illustrating the issue.

Screenshot of Google Sheets, illustrating the problem

Column F is a running balance, with the ability to carry the balance forward even if there are 1 or 2 blank lines in the data. The odd thing is that the balance listed on Row 12 is calculated by adding the value in D12 to the previous balance value in F11. Shouldn't it have the same odd decimal?

At first, I thought my formula was the issue, but then I highlighted all the values in column D and looked at the auto summary at the bottom of the page and it showed the anomaly too. (as you can see in the screenshot)

I have made the sheet publicly viewable for anyone who wants to poke around:

https://docs.google.com/spreadsheets/d/1-rMPBx9C1dx6hcBMarM4Noa5ixBUrbtZSKeou9JGK-E

If anyone has any information on why Google Sheets is doing this, I could definitely use the help!

Thanks!

Solution

  • 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