Formula Help - Need to Return A "1" Or "0" With Exceptions

[milwphil]

I'm trying to come up with a formula that will return a "1" or a "0" based on the selection of a state and person(with a few exceptions).

Currently I have:

=IF(AND(U1="Peter Griffin", OR(R63="AZ", R63="MA",R63="CA", R63=
"UT")),0,IF(U1="Quagmire",0,1))

Basically, the intent is to have if someone picks Peter Griffin return a "1". If Quagmire return a "0".

The problem I'm having is that I want any time someone selects the state AZ, MA, CA or UT to return a "0" regardless of the selection of Peter Griffin or Quagmire.

The code above gives me a 1 on Peter Griffin and 0 on Quagmire, but I can't get the exceptions to be recognized.

Hope this makes sense!

Thanks in advance!
Phil

 

[Haseeb Avarakkan]

Try;

=(U1="Peter Griffin")*AND(R63<>{"AZ","MA","CA","UT"})

 

[milwphil]

Haseeb thanks for the quick response!

Unfortunately, I'm still getting a "1" when I select Peter Griffin and "AZ"(same for CA, MA, or UT).

If AZ, CA, MA or UT are selected regardless of the name(Peter Griffin or Quagmire), I'm hoping to get a "0".

If any state and Peter Griffin are selected with AZ, CA, MA, or UT the number returned should be a 1. All states that are under Quagmire should be a "0"

Any other suggestions? Sorry for being a pain! And thanks for your help!

Phil

 

[tusharm]

Since your state tests take precedence, put them first as in the untested
=IF(OR(R63="AZ", R63="MA",R63="CA", R63=
"UT"),0,if(U1="Peter Griffin",1,IF(U1="Quagmire",0,"undefined")))

I'm trying to come up with a formula that will return a "1" or a "0" based on the selection of a state and person(with a few exceptions).

Currently I have:

=IF(AND(U1="Peter Griffin", OR(R63="AZ", R63="MA",R63="CA", R63=
"UT")),0,IF(U1="Quagmire",0,1))

Basically, the intent is to have if someone picks Peter Griffin return a "1". If Quagmire return a "0".

The problem I'm having is that I want any time someone selects the state AZ, MA, CA or UT to return a "0" regardless of the selection of Peter Griffin or Quagmire.

The code above gives me a 1 on Peter Griffin and 0 on Quagmire, but I can't get the exceptions to be recognized.

Hope this makes sense!

Thanks in advance!
Phil