Data Validation Problem

Posted by Mark F. on September 13, 2001 12:53 PM

I need a way to display an error message to the user if they enter a number with more than 4 decimal places. I have been trying to use the Custom setting in the Data Validation with the following formula:

=IF(FIND(".", E77) > 0, IF(LEN(RIGHT(E77, (LEN(E77) - FIND(".", E77)))) > 4, 0, 1), E77)

This works if the number contains a decimal, but if the user enters 1 (with no decimal) it fails. Any help would be appreciated.

Posted by IML on September 13, 2001 1:09 PM

Try the following (assuming cell A1 for validation)
=INT(A1*10000)=A1*10000

good luck

Posted by Mark F on September 14, 2001 5:05 AM

Thanks, that worked great.

Posted by Mark W. on September 14, 2001 7:38 AM

Yet, another way...

=LEN(MOD(A1,1))>6