IF statement based on two cells, with OR/AND

**john_london** · 04-19-2010, 11:45 AM

Hi guys,

This is really getting on my nerves. I have two cells with the following choices:

T7
Risk Level:
High
Med
Low
Nominal

W7
Annual Spend Range:
<$200K
$200K-$500K
$500K-$2m
>$2m

What I want is a simple formula which does the following:
If
(Cell T7 = Nominal or Low)
AND
(Cell W7 = <$200K or $200-$500)
THEN “No”
ELSE “Yes”
It keeps giving me a #value error!!

Thanks in advance!!

John

**shg** · 04-19-2010, 11:49 AM

Welcome to the forum, John.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**NBVC** · 04-19-2010, 11:50 AM

Try:

=IF(AND(OR(T7 = {"Nominal","Low"}),OR(W7<200000,AND(W7>=200000,W7<=500000))),"No","Yes")

Note: restored after title was changed.

**shg** · 04-19-2010, 12:02 PM

Or maybe,

=IF(AND(OR(T7 = {"Nominal","Low"}), W7<=500000), "No", "Yes")

**NBVC** · 04-19-2010, 12:03 PM

Yes, didn't catch the obvious...

**shg** · 04-19-2010, 12:06 PM

Actually, maybe what the OP wants is this:

=IF(AND(OR(T7={"Nominal","Low"}), OR(W7={"<$200K","$200K-$500K"})), "No", "Yes")

**john_london** · 04-19-2010, 12:16 PM

shg: that's the one, thanks!! The thing that was confusing me was using the curly brackets {

I was trying OR(W7="Low","Nominal") etc,

Muchos Gracias guys!!!!!!!