IF AND OR STATEMENT

[Lea from CA]

I want to multiply column B by 5 if Col A is not equal to Red or White or Blue.

Column A can have 100 distinct values and Red or White or Blue can be 20
different values or more.

I started with If(or(A1<>"RED",A1<>"BLUE",A1<>"WHITE"),B1*5,0). I wanted
to know if there is any other way of creating the formula.


Col A Col B
Red 10
Orange 3
Green 4
Blue 8
White 5
Red 6
Purple 7
Gold 2
Blue 7
Pink 8
Yellow 6
Black 7
Brown 1

 

[Dave Peterson]

I would add an extra worksheet that had the list of colors to ignore.

Then a formula like:

=iserror(match(a1,sheet2!a:a,0))
will return False if the color in A1 matches any cell in column A of sheet2.

FYI:
=isnumber(match(a1,sheet2!a:a,0))
would return true if there a match

So

=if(iserror(match(a1,sheet2!a:a,0)),b1*5,0)

or more simply:
=iserror(match(a1,sheet2!a:a,0))*b1*5

(excel will coerce the true to 1 and false to 0 when it does the multiplication.

 

[Bernard Liengme]

=B1*5*(A1<>"Red")*(A1<>"Blue")*(A1<>"White")
OR
=5*B1*(1-(ISNUMBER(MATCH(A1,{"red","white","blue"},0))))
OR
=B1*5*ISNA(MATCH(A1,{"RED","WHITE","BLUE"},0))
In general, when you need conditional math it is not necessary to use IF
best wishes

 

[Jim Thomlinson]

Your boolean is should be AND not OR. If A1 is not equat to Red and A1 is not
equal to White and A1 is not equal to Blue then... times 5. Another way would
be

If A1=Red, or A1= White or A1=Blue then nothing, else multiply by 5.