Data Validation issue (Decimal Places)

chadmiller

New Member
Joined
Jan 1, 2007
Messages
23
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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, allow|Custom, use the following formula
4) =LEN(MID(B1,FIND(".",B1)+1,255))=1

that should do it.

I hope this helps.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top