1

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!

Todd Powers
  • 127
  • 1
  • 7
  • It's not a bug. It's an artifact of how computers "compute." It's called "floating point arithmetic"; and it's a result of how numbers are represented in binary as opposed to the physical world or pure math. – Erik Tyler Sep 14 '22 at 11:50

1 Answers1

2

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
  • Did you look at the formulas in the sheet? There are no division or multiplication operations happening anywhere in the sheet. Just adding and subtracting. Nothing that would introduce any floating point operations at all. Like I said, I'm not looking for the ROUND() workaround. I want to know why adding one integer to another causes minute decimal anomolies. – Todd Powers Sep 14 '22 at 23:12
  • @ToddPowers floating point operation is introduced in your sheet with every numeric value you store in any cell. its the way how numeric numbers are stored not the result of users operation – player0 Sep 14 '22 at 23:16