Google-sheets – Google Sheets adding weird number in 11th decimal place

formulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-timestamp

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.

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.

Best Answer

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.