Data Validation : Multiple If condtions

[chandrashekar]

Hello,

am trying below formula in data validation{Custom) but not able to exact result.

Formula:
=IF(AND(D3="Excellent",F3>=10,F3<=15),TRUE,IF(AND(D3="Good",F3>=8,F3<=10),TRUE,IF(AND(D3="Average",F3>=5,F3<=7),TRUE,IF(AND(D3="Poor",F3>=1,F3<=4),TRUE,FALSE))))

Regards,
Chandrashekar B

 

[6StringJazzer]

Showing us a formula that you said doesn't work doesn't give us enough information to tell you what will work.

Start by telling us what cell contains this data validation rule.

Then explain what you want the rule to do instead of what this one does.

By the way, a data validation formula returns TRUE or FALSE. You can do this with Boolean logic, with no IF. However, this just reformulates the logic you started with so I doubt this will solve your problem.

OR(AND(D3="Excellent",F3>=10,F3<=15),AND(D3="Good",F3>=8,F3<=10),(AND(D3="Average",F3>=5,F3<=7),AND(D3="Poor",F3>=1,F3<=4))

I also note that F3 can be 10 for both Good and Excellent. Is that correct?

 

[chandrashekar]

Hello,

am trying to validate value based on below condition. If user type invalid information then Data validation should throw error.

Good	8-10
Excellent	11-15
Poor	1-4
Average	5-7

I have changed formula as per your input but still it is not working.

=OR(AND(D3="Excellent",F3>=11,F3<=15),AND(D3="Good",F3>=8,F3<=10),AND(D3="Average",F3>=5,F3<=7),AND(D3="Poor",F3>=1,F3<=4))

Regards,
Chandrashekar B

 

[chandrashekar]

Hello,

am trying to validate value based on below condition. If user type invalid information then Data validation should throw error.

Good	8-10
Excellent	11-15
Poor	1-4
Average	5-7

I have changed formula as per your input but still it is not working.

=OR(AND(D3="Excellent",F3>=11,F3<=15),AND(D3="Good",F3>=8,F3<=10),AND(D3="Average",F3>=5,F3<=7),AND(D3="Poor",F3>=1,F3<=4))

Regards,
Chandrashekar B

Thanks it is working now.

Regards,
Chandrshekar B