Question Data Validation issue (Decimal Places) ( MrExcel Message Board Excel Questions ) Updated: 20080419 23:13:54 (7) 

Data Validation issue (Decimal Places)
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.


Answers: Data Validation issue (Decimal Places) ( MrExcel Message Board Excel Questions ) 

Data Validation issue (Decimal Places)
Quote:
Originally Posted by chadmiller
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.

Hi Chad Miller:
How about trying the following DataValidation formula for cell A1:
=LEN(MID(A1,FIND(".",A1)+1,255))=1
I hope this helps.
Yogi Anand


Data Validation issue (Decimal Places)
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
LxQ


Data Validation issue (Decimal Places)
Perhaps:
=AND(TRUNC(G29,1)=G29,G29>=0,G29<=100)
HOTPEPPER


Data Validation issue (Decimal Places)
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.
chadmiller


Data Validation issue (Decimal Places)
Quote:
Originally Posted by chadmiller
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.

Hi Chad:
To apply DataValidation to cells B1:B10 ...
1) select cells B1:B10
2) make cell B1 as the ActiveCell
3) Invoke DataValidation ... settings, allowCustom, use the following formula
4) =LEN(MID(B1,FIND(".",B1)+1,255))=1
that should do it.
I hope this helps.
Yogi Anand


Data Validation issue (Decimal Places)
Yogi,
That doesn't limit the range to 0100.
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)
HOTPEPPER


Data Validation issue (Decimal Places)
Quote:
Originally Posted by HOTPEPPER
Yogi,
That doesn't limit the range to 0100.
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)

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.
Yogi Anand


 Source:  Previous Question: MrExcel Message Board Excel Questions  Next Question: MrExcel Message Board Excel Questions 

