Identify the value with highest number of decimal values-CodePudding

I have a range of values and I want to count the decimal points of all values in the range and display the max count. the formula should exclude the zeroes at the end(not count ending zeroes in the decimal points).

for example, in the above sample, in the whole range the max of count of decimal places is 4 excluding the ending zeroes. so the answer is 4 to be displayed in cell D2

I tried doing regex, but do not know how do I do it for a whole range of values.

Please help!

CodePudding user response：

try:

=INDEX(MAX(LEN(IFERROR(REGEXEXTRACT(TO_TEXT(A2:C4), "(\.. )")*1))-2))

CodePudding user response：

TEXT(number, format) requires a format whose max. number of decimal places has to be specified. If you think about it, there is no way around this, because formulas like =1/3 can have infinitely many decimal places.
Therefore, first decide on the max, precision for your use-case (here we use 8). Then use below function which works independently from your documents formatting and language:

=INDEX(MAX(
  LEN(REGEXEXTRACT(
    TEXT(ABS(A2:C4); "."&REPT("#";8));
    "[,.].*$"
  ))-1
))

We subtract -1 since LEN(REGEXEXTRACT()) also counts the decimal separator (. for english, , for many others) .

Everything after the 8th decimal place is ignore. If all your numbers are something like 123.00000000987 the computed max. is 0. If you prefer it to be 8 instead, then add ROUNDUP( ; 8):

=INDEX(MAX(
  LEN(REGEXEXTRACT(
    TEXT(ROUNDUP(ABS(A2:C4);8); "."&REPT("#";8));
    "[,.].*$"
  ))-1
))