Multiple IF criteria
Posted by Ken May on January 07, 2002 11:41 AM
Is there a way in an IF formula can meet more than 1 criteria or also not match more than 1?
I am not talking about nesting multiple IF formulas, but if a cell equals 100 AND 200, or if a cell equals 100 OR 200 then whatever.
Posted by Mark W. on January 07, 2002 11:48 AM
A cell cannot "equal 100 AND 200". Use the
formula, =IF(OR(A1={100,200}),"Yes","No"), to
test if the cell A1 "equals 100 OR 200".
Posted by Tim Francis-Wright on January 07, 2002 12:43 PM
It sounds like you are looking for an exclusive-or function. For two conditions, this is:
=if(and(or(a1=100,b1=200)),not(and(a1=100,b1=200)),"Yes","No")
For three or more conditions, this is a VBA question, and my brain hurts too much to try it.
HTH
Posted by Mark W. on January 07, 2002 1:11 PM
Simplified XOR...
{=NOT(PRODUCT((A1:B1={100,200})+0))}
No need for brain strain or VBA with 3 or more
conditions...
{=NOT(PRODUCT((A1:C1={100,200,300})+0))}
{=NOT(PRODUCT((A1:D1={100,200,300,400})+0))} ...
Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.
Posted by Mark W. on January 07, 2002 1:53 PM
Oops! Found a semantical error...use these instead...
{=SUM((A1:B1={100,200})+0)=1}
{=SUM((A1:C1={100,200,300})+0)=1}
{=SUM((A1:D1={100,200,300,400})+0)=1}
