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

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.



Lea from CA wrote:

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

=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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Lea from CA" wrote in message
...
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

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.
--
HTH...

Jim Thomlinson


"Lea from CA" wrote:

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