2

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??

TRX
  • 465
  • 1
  • 8
  • 17
  • 2
    Floating-point error :-) More reading [here](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result) and [here](https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/). – BigBen Dec 18 '20 at 05:32
  • This question is not clear. Do you want to get the text length of the text returned by a formula computation? If so, won't the LEN and TEXT functions accomplish that for you? – Mark Fernandes Dec 18 '20 at 12:30
  • 1
    round the result.. before get the length. ( : – p._phidot_ Dec 18 '20 at 18:12
  • @MarkFernandes Since `5` is in the cell `B1`, I just wonder to know why `=LEN(B1)` returns `16`... – TRX Dec 21 '20 at 07:04
  • @TRX: I'm not sure why either. It could be that the `LEN` function is trying to calculate the text length of the formula rather than the result. If the `LEN` function is mistakenly operating on the assumption of a 2-byte unicode encoding, when in fact a 1-byte ASCII encoding is being used, then that could explain the return value of 16 [half of 32, 32 being the formula character length). – Mark Fernandes Dec 23 '20 at 08:56

1 Answers1

2

The reason =LEN(B1) returns 16 is because the value in cell B1 may be displayed as 5, but it is actually stored internally as 4.99999999999989 - and that number has 16 characters in it, when evaluated as a string by LEN().

You can see this for yourself by repeatedly clicking on the "Increase Decimal" button:

enter image description here

Here, the display eventually has enough decimal places to reflect the underlying value (the LEN function ignores trailing zeroes - again, those are display zeroes not stored zeroes).

You can also see the same thing if you simply enter 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

The loss of precision from your displayed 5 to the actual stored 4.99999999999989 is indeed because of how floating point math works, as mentioned by BigBen.

See also Is floating point math broken? for a broader discussion.

andrewJames
  • 19,570
  • 8
  • 19
  • 51