IF formula with VALIDATION Rules

  • Thread starter Thread starter LSTOOPS
  • Start date Start date
L

LSTOOPS

Is it possible to great an IF Statement that allows for validation? I am
putting together a performance rating spreadsheet where an employee is
rated 1,2,3 or 4. In the next column, a percentage amount will be
entered which should be based upon the rating. For example, if a
rating of 1 is entered, the percentage amount should be between the
range of 90 - 110%. If a rating of 2 is entered, the percentage amount
should be 80 - 105%.
 
Hi

For cell C2, with rate in B2, set for Data.Validation.Custom the formula to
something like:
=AND(C2>=CHOOSE(B2,0.9,0.8,0.7,0.6),C2<=CHOOSE(B2,1.1,1.05,1,0.95))
 
One way using the custom option under data validation allow

=IF(A1=1,AND(B1>=90%,B1<=110%),IF(A1=2,AND(B1>=80%,B1<=105%),IF(A1=3,AND(B1>
=70%,B1<=100%),IF(A1=4,AND(B1>=60%,B1<=95%)))))

Another way

=((A1=1)*(B1>=90%)*(B1<=110%))+((A1=2)*(B1>=80%)*(B1<=105%))+((A1=3)*(B1>=70
%)*(B1<=100%))+((A1=4)*(B1>=60%)*(B1<=95%))

adapt to fit your range and the conditions, I just assumed the conditions
for 3 and 4. You can select a whole range and apply the validation, the
downside is that the users can paste any value..
 
Back
Top