0

Can anyone explain this? I have 18 hardcoded numbers - that is, typed in, not as a result of a formula. [B1 thru B18]
I have a cell [D1] that sums those 18 numbers.
I have eleven other hardcoded numbers [cells D3 thru D13]
11 cells subtract D1 from each of D3 thru D13 [cells F3 thru F13].
Somehow, Sheets is adding weird numbers in the 11th decimal place for no particular reason.

screen shot from google sheets

In the example, all numbers are extended out to 30 decimal places just to prove there isn't a rounding error.
Again, all numbers in column B are hard coded.
If the difference is more than 10,000, then no weird decimals are added.
I assume it is related to floating point numbers, but this just feels wrong.
I retyped everything in a fresh sheet to confirm it happens consistently.

I will include the list of numbers in a comment to see if that helps anyone.

  • Here are the numbers in column B 6510 7984.2 89 142 3719.4 89 252 2244 89 2755.02 9097 455.04 89 89 89 8633.56 194.94 194.3 – Ben Adams Mar 30 at 13:51
0

First of all, extending numbers to 30 decimals prooves nothing (not even rounding errors).

Secondly, Google Sheets is not able to handle more than 15 decimals (eg. everything after that is irrelevant)

Thirdly, all numbers in Google Sheets acts also as placeholders for dates - for example:

1      = 31/12/1899
100    = 09/04/1900
-1000  = 04/04/1897
43555  = 31/03/2019
100000 = 14/10/2173

Also, there is a certain (+/- 10000) threshold around today's date which reflects in such manner that Google Sheets predicts it's a date, not a number. So to answer your question: those weird numbers around 11th decimal are caused because Google Sheets converts those numbers to dates eg. you summing date values instead of numbers.

0

Therefore, to correct it, you will need to force numbers over date values which will give you:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.