Strange and Incorrect Excel Formulas Result

Question

I want to get the length of a formula result, but it returns incorrectly.

For example, I put 1005 in the cell A1, put the formula =(A1/1000-FLOOR(A1/1000,1))*1000 into B1 and the result is 5, that's correct. But when I put the formula =LEN(B1) into the cell C1, the result is 16? Even worse, the formula =REPT(0,3-LEN(TEXT(B1,"0")))&(B1) or =REPT(0,3-LEN(TEXT((A1/1000-FLOOR(A1/1000,1))*1000,"0")))&((A1/1000-FLOOR(A1/1000,1))*1000) returns 004.99999999999989??🤔

Answer 1

Because the value in cell B1 may appear to be 5, but it is actually kept internally as 4.99999999999989 - and that number has 16 characters when evaluated as a string by LEN - =LEN(B1) returns 16. ().

You may observe this for yourself by pressing the "Increase Decimal" button several times:

The display ultimately gets to the point where it has enough decimal places to indicate the underlying value (the LEN function ignores trailing zeroes - again, those are display zeroes not stored zeroes).

You may achieve the same result by typing the following formula into a cell:

=1.005-1

This actually has 19 characters in it, not 16, because we have not multiplied by 1,000:

0.00499999999999989