CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Russ
Russ

Reputation: 710

Why is the last digit of my formula being chopped off?

I am calculating a decimal field and Google Sheets appears to cut off the last digit of Column D.

Formula being used for Column A: =B2+(C2/10000)+(D2/10000000)

When I have it in Column A as (C2/10000)+(D2/10000000) it calculates as expected, but when I add column B into the mix, that's when it chops off the last digit of column D.

When I concatenate the field using =CONCATENATE($B2, ".", $C2, $D2) it looks like it works fine. So why doesn't the first formula work?

Column A Column B Column C Column D
202400160.2024310 202400160 2024 311
999900236.2024300 999900236 2024 303
202100002.2024300 202100002 2024 304
201500001.2024300 201500001 2024 309
999900054.2024300 999900054 2024 306

Upvotes: 0

Views: 51

Answers (1)

doubleunary
doubleunary

Reputation: 19145

Google Sheets and most other spreadsheet programs use the IEEE 754 double-precision floating point number standard. This standard specifies an accuracy of 15.95 decimal digits.

A value like 202400160.2024311 requires 16 decimal digits, which means that it cannot be represented accurately as a floating point number. In the use case presented in the question, you lose the last digit.

See Why are floating point numbers inaccurate?.

Upvotes: 2

Related Questions