Data Validation - 2 decimal places

[Darren L]

Is it possible to restrict data entry to numercial values to 2 decimal places
- perhaps using data validation?

 

[Bob Phillips]

Custom type with a formula of

=IF(ISNUMBER(G1),IF(NOT(ISNUMBER(FIND(".",G1))),TRUE,IF(LEN(G1)-FIND(".",G1)<3,TRUE)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Darren L]

Superb.

But I need to restrict data entry to only numercial values greater than zero
to 2 decimal places - I can't seem to amend the formula to do this.

 

[Bob Phillips]

=IF(ISNUMBER(G1),IF(G1>0,IF(NOT(ISNUMBER(FIND(".",G1))),TRUE,IF(LEN(G1)-FIND(".",G1)<3,TRUE))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Darren L]

Genius - works perfectly. Thanks very much

=IF(ISNUMBER(G1),IF(G1>0,IF(NOT(ISNUMBER(FIND(".",G1))),TRUE,IF(LEN(G1)-FIND(".",G1)<3,TRUE))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)