Using And(OR

[sadsfan]

Hi, I'm struggling using the and(or function in Excel 2003. I trying to return a value of 1 if cell cg5 and cell am5 match particular criteria. I want to check:
if cell cg5=1 and am5= 19 or 21 or 23
or if cell cg5=2 and am5= 25 or 27 or 29
or if cell cg5=3 and am5= 31 or 33 or 35

This my formula so far, I keep getting a FALSE value, where am I going wrong?

=and(or(cg5=1),or(am5=19,am5=21,am5=23)*1,1,and(or(cg5=2),or(am5=25,am5=27,am5=29)*1,1,and(or(cg5=3),or(am5=31,am5=33,am5=35)*1,1)))

 

[T. Valko]

Hi, I'm struggling using the and(or function in Excel 2003. I trying to return a value of 1 if cell cg5 and cell am5 match particular criteria. I want to check:
if cell cg5=1 and am5= 19 or 21 or 23
or if cell cg5=2 and am5= 25 or 27 or 29
or if cell cg5=3 and am5= 31 or 33 or 35

This my formula so far, I keep getting a FALSE value, where am I going wrong?

=and(or(cg5=1),or(am5=19,am5=21,am5=23)*1,1,and(or(cg5=2),or(am5=25,am5=27,am5=29)*1,1,and(or(cg5=3),or(am5=31,am5=33,am5=35)*1,1)))

Here's one way...

=--OR(AND(CG5=1,OR(AM5={19,21,23})),AND(CG5=2,OR(AM5={25,27,29})),AND(CG5=3,OR(AM5={31,33,35})))

 

[JB_Scotland]

=or(and(cg5=1,or(am5=19,am5=21,am5=23)),and(cg5=2,or(am5=25,am5=27,am5=29)),and(cg5=3,or(am5=31,am5=33,am5=35)))

 

[asad]

Here's one way...

=--OR(AND(CG5=1,OR(AM5={19,21,23})),AND(CG5=2,OR(AM5={25,27,29})),AND(CG5=3,OR(AM5={31,33,35})))

What does this "--" do in the formula you have given? Will the formula work without them also?

 

[T. Valko]

What does this "--" do in the formula you have given? Will the formula work without them also?

That will convert the boolean TRUE or FALSE returned by OR(...) to 1 or 0.

--TRUE = 1
--FALSE = 0

See this...

http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[asad]

Thanks Biff. Now I know .
Asad

 

[T. Valko]

Thanks Biff. Now I know .
Asad

You're welcome. Thanks for the feedback!

 

[sadsfan]

Thanks Biff, that worked perfectly.

 

[T. Valko]

Thanks Biff, that worked perfectly.

You're welcome. Thanks for the feedback!