Data Validation Query: Restrict the data input to two decimal places

**The_Snook** · 12-01-2012, 04:23 PM

Hi,

Is it possible to add a data validation to a cell which restricts the user from inputting more than 2 decimal places? I would have thought it would be easy to do but i'm struggling?!?!

Cheers,

Snook

**FDibbins** · 12-01-2012, 04:34 PM

it seems you cant restrict the number of decimal places with DV. what you could do to overcome this is to reference that cell with a rounddown() or roundup()

**Ace_XL** · 12-01-2012, 04:48 PM

Try this in custom data validation

Please Login or Register  to view this content.

**The_Snook** · 12-01-2012, 05:20 PM

I think you've cracked it Ace_XL

Now i've just got to try and get my head around how it works!

FDibbins - Thanks for your suggestion it got me thinking in a different way to a similar problem I had which i've now managed to solve using the ROUND formula.

Rep points all round

**FDibbins** · 12-01-2012, 05:25 PM

we are happy to help, and thanks for the rep

**Ace_XL** · 12-01-2012, 05:30 PM

Thanks for the rep

Here is how it works..

In case of a number without decimal points, it allows for any characters
=IF(ISERROR(FIND(".",A1)),LEN(A1)>0

in case it finds a decimal point (the FIND function) the <3 ensures that the number of decimals are less than or equal to 2

One flaw with this would be that it would allow even non-numeric input. Hence a refined verison is as below

Please Login or Register  to view this content.

**JBeaucaire** · 12-01-2012, 05:42 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.