IF - AND - OR Combination

[carlosbourn]

Hi,

I am trying the following formula:

=IF(AND(J6="Yes",OR(B6<>"Nigeria",B6<>"Togo")),30%,0)

In order to do the following:

If J6 = "Yes" and B6 does not equal "Nigeria" or "Togo" then return 30%, otherwise 0.

But if I test it by having J6 as "Yes" and insert either "Nigeria" or "Togo" into B6, it still returns 30%...?

Many thanks in advance for any help.

 

[FryGirl]

Maybe you do not need the OR

=IF(AND(J6="Yes",B6<>"Nigeria",B6<>"Togo"),30%,0)

=IF(AND(J6="Yes",B6<>{"Nigeria","Togo"}),30%,0)

 

[carlosbourn]

Oh dear, seems so simple now!

Thanks a lot

 

[FryGirl]

You're welcome. Glad to help.

 

[carlosbourn]

Hello again, I'm a little stuck again!

I now need to reverse the formula to something like this because the number of possible variables in B6 has increased so rather than stating <> I thought it would be better to state =:

=IF(AND(J6="Yes",B6="Indian Ocean etc",B6="Togo"),30%,0)

However, if I have J6 as "Yes" and insert either "Indian Ocean etc", it returns 0 instead of 30%...? is this because this way around cell B6 has to equal both "Indian Ocean etc" and "Togo"?

Many thanks in advance for any help.

 

[FryGirl]

Try

=IF(AND(OR(B6={"Indian Ocean","Togo"}),J6="Yes"),30%,0)

If B6 contains either Indian Ocean OR Togo AND J6 = "Yes"

 

[carlosbourn]

Perfect - thank you so much for your help today, FryGirl