How to check number of decimals [SOLVED]

[smanjunath]

Hi,

In one of the load templates we require the user to enter quantity in lbs in one of the column, and the user need to enter the value only upto 3 decimal points.

We could have sent the template with input restrictions through data validations, but apparently we did not. Now how do i check whether there are entries in the column that have more than 3 places of decimal ?

that is an entry like 9999.999, 1.123 are ok, but 1.1234, 999.9991 is NOT ok.


Hope my question was clear..


Thanks in advance

 

[ThrottleWorks]

Hi Manjunath,


I will share what I just tried.


Suppose your value is in Cell A1.


Then the formula will be


=LEN(A1)-FIND(".",A1)


This will give you the number of how many decimals you have.


This will work if the values are in decimal.

 

[smanjunath]

Thanks, it works, I can use the result value (if >3) for conditional formatting...

But has a minor problem, If the value in A1 is a whole number (no decimals) as you have pointed out, it returns #N/A, i could use 'iferror' to get rid of it, though.


meanwhile i was using this formula


=(A1*1000)-INT(A1*1000) > 0


and the result would be more than ZERO, if the number in A1 had a 4th decimal


Thanks for the response..

 

[ThrottleWorks]

Hi Manjunath,


Thanks for the feedback, glad that I could assist you.