Formula or Cond. Format for Multiple And/Or Criteria

[Jacked0528]

Good Afternoon All,

Looking to create a single output cell that's looking at two input cells based on their criteria.

For instance, cell AT9 contains a dropdown list (Tightened, Normal, Reduced)
Cell AT12 contains a # (1 to 200+)

In cell AR14 I'd like it to look for the following criteria and I'm not sure how to get it to do it:
If AT9 = Tightened and AT12=<5..."No", >5..."Yes"
If AT9 = Normal and AT12=<10..."No", >10..."Yes"

 

[etaf]

how about
=IF( OR( AND(AT9 = "Tightened", AT12<=5), AND(AT9 = "Normal",AT12<=10) ), "No","Yes")

 

[Jacked0528]

When I apply that, it works flawlessly for the "Normal" testing (i.e., anything with Normal and less than 10 is No and greater than is Yes). But when I change Normal to Tightened everything is Yes no matter when number I apply to AT12.

 

[Joe4]

When I apply that, it works flawlessly for the "Normal" testing (i.e., anything with Normal and less than 10 is No and greater than is Yes). But when I change Normal to Tightened everything is Yes no matter when number I apply to AT12.

Did you try re-typing the formula yourself, or did you use Copy/Paste?
Try using Copy Paste.

If I put "Tightened" in cell AT9 and 4 in cell AT12, the formula returns "No" as expected.

If it is still not working for you, tell us your exact values in AT9 and At12 in your example that are returng "Yes" when they should be returning "No".

 

[Jacked0528]

You're right, I must have typed something wrong when I re-typed it. Copy/Paste took care of that.

Second question, if my AT9 has a third dropdown option for "Reduced", is there a way to get AT14 to say N/A?

 

[etaf]

=IF( OR( AND(AT9 = "Tightened", AT12<=5), AND(AT9 = "Normal",AT12<=10) ), "No","Yes")
change to

=IF(AT9="Reduced", "N/A", IF( OR( AND(AT9 = "Tightened", AT12<=5), AND(AT9 = "Normal",AT12<=10) ), "No","Yes"))

 

[Jacked0528]

Absolutely amazing. I never cease to be amazed. Thank you so much

 

[etaf]

you are welcome

if you want an error type #N/A rather then the text "N/A"
change to
=IF(AT9="Reduced", NA(), IF( OR( AND(AT9 = "Tightened", AT12<=5), AND(AT9 = "Normal",AT12<=10) ), "No","Yes"))