Data Validation issue (Decimal Places)

Thanks:  0
Likes:  0

# Thread: Data Validation issue (Decimal Places)

1. ## 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”

2. ## Re: 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”

How about trying the following DataValidation formula for cell A1:

=LEN(MID(A1,FIND(".",A1)+1,255))=1

I hope this helps.

3. ## Re: 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

4. ## Re: Data Validation issue (Decimal Places)

Perhaps:

=AND(TRUNC(G29,1)=G29,G29>=0,G29<=100)

5. ## Re: 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.

6. ## Re: 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.

To apply DataValidation to cells B1:B10 ...

1) select cells B1:B10
2) make cell B1 as the ActiveCell
3) Invoke DataValidation ... settings, allow|Custom, use the following formula
4) =LEN(MID(B1,FIND(".",B1)+1,255))=1

that should do it.

I hope this helps.

7. ## Re: Data Validation issue (Decimal Places)

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)

8. ## Re: Data Validation issue (Decimal Places)

Originally Posted by HOTPEPPER
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)
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•