This is a discussion on Data Validation issue (Decimal Places) within the Excel Questions forums, part of the Question Forums category; Aloha Excel Guru’s. I am hoping someone can provide a way to provide better validation than Excel’s default decimal validation ...
Aloha Excel Guru’s.
I am hoping someone can provide a way to provide better validation than Excel’s default decimal validation (Excel 2007). I have cells configured to only accept decimal values between 0 and 100. I need to further restrict the input values to one decimal place.
Desired Result: Accept “70.1” as valid data but not “70.11”
Thanks in advance.
if you want to enter this in G29.. like I just happened to be in that cell.. then use this formula:
=IF(G29=INT(G29*10)/10,IF(G29>=0, IF(G29<=100,TRUE,FALSE),FALSE),FALSE)
First, this takes the integer of g29*10, so it actually moves the decimal point, then divides by 10,.. and if that's equal to what was entered, then there were no more than 1 decimal points entered...
then I had to add two more IF statements just to limit it between 0 and 100
Perhaps:
=AND(TRUNC(G29,1)=G29,G29>=0,G29<=100)
Office 2007/2010/2013
Thank you, I seem to get a few of your solutions to work in a different cell but can I put a formula in the actual data validation seciton?
Hopefully this is not a silly questions cuz I can't find an option that will accept the formula.
Yogi,
That doesn't limit the range to 0-100.
Although the op may not want whole numbers, your formula doesn't allow them.
Perhaps if a forced decimal place is required:
=AND(LEN(MID(B1,FIND(".",B1)+1,255))=1,B1>=0,B1<=100)
Last edited by Scott Huish; Apr 17th, 2008 at 08:43 PM.
Office 2007/2010/2013
Hi HOTPEPPER:
Thanks for pointing out about the 0 to 100 range. I was focussing on 1 significant digit after the decimal point and overlooked the 0 to 100 range issue. That can be added as AND condition to my formula.
My proposed solution is one way and not necessarily the best, the most efficient, or the shortest.
Like this thread? Share it with others