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:
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
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.
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:
Last edited by Scott Huish; Apr 17th, 2008 at 08:43 PM.
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.