Solved If statement using data validation in Excel 2010

April 23, 2014 at 11:09:21
Specs: Windows 7
I am trying to use the data validation function to allow certain range of numbers based on a particular category.
Category Range Allowed
Superior >3.5%
Average 2.6% - 3.5%
Below 0.0% - 2.5%
Not Eligible 0%

See More: If statement using data validation in Excel 2010

Report •

#1
April 23, 2014 at 12:23:38
Do you expect the users to manually enter a % value or do you want to provide a Drop Down list of percentages within those ranges for them to choose from?

If you want to provide a list, what would the interval be, e.g.

2.5, 2.6, 2.7 or something more precise than that (2.5, 2.51, 2.52, etc.)

What is the maximum value that is allowed? >3.5% could go all the way to infinity.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 23, 2014 at 12:46:35
The user would manually enter a %. There isn't a limit on the maximum allowed for the superior category.

Report •

#3
April 23, 2014 at 13:26:31
✔ Best Answer
This Data Validation formula, entered the Custom field seems to work. I don't know if it's the most efficient method, but it seems to meet your criteria.

The assumption is that your Categories are entered in A2 and the Percentage is entered in B2.

Note: Once a value in B2 is accepted as valid, changing the Category in A2 is not going to automatically make the Percentage in B2 "invalid". The Data Validation is only performed when B2 is edited.

=IF(A2="Superior",IF(B2>3.5%,TRUE),
IF(A2="Average",IF(AND(B2>=2.6%,B2<=3.5%),TRUE),
IF(A2="Below",IF(AND(B2>=0,B2<=2.5%),TRUE),
IF(A2="Not Eligible",IF(B2=0,TRUE)))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •
Related Solutions


Ask Question