CODEWITHSUNDEEP
excelexcel-formula
Ilyas Esa
Ilyas Esa

Reputation: 11

Is there a excel formula that can lookup the whole column K for any values that have more than 2 decimals?

I need to figure out if any of the values in Column K have more than 2 decimals?

So the formula I need would basically say TRUE if any of the values have more than 2 decimals or return the number of values that have more than 2 decimals or even better if it can even return the cell or row location(s)

enter image description here

I tried =IF(LEN(RIGHT(K2,LEN(K2)-FIND(".",K2)))>2,TRUE,FALSE) but it only gives me the results on a row-by-row basis. I also tried to do that by summing up the whole column but ended up getting some fake results.

Upvotes: 0

Views: 498

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

This seems like it might be an x-y problem.

  • Is this merely an exercise? Or is there a specific overlying problem you are trying to solve?
  • Note that decimal numbers cannot always be expressed exactly in a binary system like Excel.

Having written that, to get a count of values with more than two decimals try:

=SUM(N(MOD(myRange*100,1)<>0))

You don't write how you wish to return the addresses; the following will return the addresses as a vertical array (requires Excel 365):

=LET(
    a, ADDRESS(
        (MOD(myRange * 100, 1) <> 0) * ROW(myRange),
        COLUMN(myRange)
    ),
    FILTER(a, NOT(ISERROR(a)))

Upvotes: 2

Related Questions