Solved

# Format Percentage Cells, Restrict Values >100.001

Posted on 2011-10-18
196 Views
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
Question by:monbois

LVL 43

Assisted Solution

0

LVL 50

Accepted Solution

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

Works perfectly! Thanks!
0

## Featured Post

### Suggested Solutions

52/53 week year end date formula 5 35
Sum Per Month 7 23
rank minimum order 9 19
Calculating Percentile Value inside Excel. 2 15
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…