[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Format Percentage Cells, Restrict Values >100.001

Posted on 2011-10-18
3
Medium Priority
?
202 Views
Last Modified: 2012-05-12
Hi,

I have a percentage dilemma with Excel: I have ranges formatted as percentages and I'm tring to restrict the user from entering values greater than 100.001. Using Excel's Data Validation feature, the closest I can come to it and still force users to enter numbers and not text is:

     =AND(ISNUMBER(I7),I7<=100.001)

Although this looks like it would work, it doesn't. Users can't enter text, but it still allows them to enter values greater than 100.001. I tried reversing the formula but that didn't work, and neither did using Whole Numbers Data Validation - that wouldn't let me enter squat on a cell formatted as a percentage no matter what the maximum number was.

Any help would be greatly appreciated!

Thanks!
0
Comment
Question by:monbois
3 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 36988115
Try 1.00001 instead of 100.001
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 400 total points
ID: 36988124
Percentages can be a little odd - when you format a cell as % then entry can be 50 but excel stores that as 0.5....so you need to keep numbers in the range 0 to 1.00001

Try using the "Decimal" option then "between" and use those 2 values - 0 and 1.00001

that won't allow text either

regards, barry
0
 

Author Closing Comment

by:monbois
ID: 36988415
Works perfectly! Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question