Hi,
I have a problem with telling the data validation tool to restrict data input to % that fits between 0 and 30.
I set the number format in cells to percentage and then I used the whole number type of data validation. I'm stuck on the formulas to tell it more than 0 less than 30.
I'd appreciate your help.
Regards,
Nina
You would have to use Decimal since percentages are decimals.
Between 0% and 30% is the same as between 0.00 and 0.30
You will have to a database validation to limit the percentages to decimal values between 0.0 and 0.3.
Now if you only want WHOLE percents, 0%, 1%, 2% ... and not something like 5.5%, then use a helper cell with the formula B2 =A2*100=INT(A2*100) then use a custom validation against this value, like allow entry into A2 when B2 = TRUE.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Good catch dflak.
Here's another option if you wouldn't want to allow something like 25.5% (using a custom rule):
Highlight the cells that you want to apply the data validation to (let's say that's H5:I100) > Data > Data Validation > Allow: Custom > Formula:
=AND(H5>=0,H5<=0.3,MOD(H5*100,1)=0)
OK
Last edited by 63falcondude; 01-17-2019 at 03:40 PM.
Thank you all, to clarify I do want to allow 25%, but I don't want to allow something like 25,3%. I'll try using the methods you suggested.
You must have seen my response from post #4 before I edited it. The formula that is in there now should work for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks