if...or....function

[Mgt]

I need to create a function that says:

If cell x is either 2,4,6,or 8, then type 'blue' in cell y
and if cell x is either 1,3,5 or 7, then enter 'yellow' in cell y
and if cell x is either 10,12,14,or 16, then enter 'green' in cell y
etc....

 

[Bob Umlas Excel MVP]

=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YELLOW",IF
(OR(x={10,12,14,16}),"GREEN")))
where x is the cell containing the values, like:
=IF(OR(A1={2,4,6,8}),"BLUE",IF(OR(A1={1,3,5,7}),"YELLOW",IF
(OR(A1={10,12,14,16}),"GREEN")))

 

[Sandy Mann]

You dan't say what you want to do if cell x is other than the numbers that
you specify. Assuming that cell x will never be anything other than your
requirements then try:

=IF(A1>9,"Green",IF(MOD(A1,2)=1,"Yellow","Blue"))

HTH

Sandy

 

[Mgt]

thanks--this was the most helpful by far!
But I am still encountering problems after about 7 groups (are these called
arrays?)
Anyway I tried to do this and got a Value error:
=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YELLOW",I
(OR(x={10,12,14,16}),"GREEN",IF(OR(x={11,13,15,17}),"RED",IF(OR(x={100,200,300,400}),"PINK",IF(OR(x={500,600,700,800}),"black"))))))=IF(OR(x={45,55,65,75}),"BROWN...etc...i.e. group 3 groups of seven parenths, but not working...

 

[Harlan Grove]

Mgt wrote...
....

But I am still encountering problems after about 7 groups (are these
called arrays?)
Anyway I tried to do this and got a Value error:
=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YELLOW",
IF(OR(x={10,12,14,16}),"GREEN",IF(OR(x={11,13,15,17}),"RED",
IF(OR(x={100,200,300,400}),"PINK",IF(OR(x={500,600,700,800}),
"black"))))))=IF(OR(x={45,55,65,75}),"BROWN
...etc...i.e. group 3 groups of seven parenths, but not working...

Show the *ENTIRE* formula. What you've shown looks like a mistake at
the '=IF(' piece since it's unlikely you're really checking different
colors for equality against each other.

If you have lots of these groups of 4 values (more than 6), you'll
run out of nested function call levels. One work-around would be

=IF(OR(x={2,4,6,8}),"BLUE","")
&IF(OR(x={1,3,5,7}),"YELLOW","")
&IF(OR(x={10,12,14,16}),"GREEN","")
&IF(OR(x={11,13,15,17}),"RED","")
&IF(OR(x={100,200,300,400}),"PINK","")
&IF(OR(x={500,600,700,800}),"black","")
&IF(OR(x={45,55,65,75}),"BROWN","")
&...

 

[Gord Dibben]

Mgt

You have run up against the 7 nested function limit in Excel.

For more on this see Chip Pearson's site.

http://www.cpearson.com/excel/nested.htm

Perhaps you could think about event code with Case Select.

Chip has info on that also.

http://www.cpearson.com/excel/events.htm


Gord Dibben Excel MVP

 

[Leo Heuser]

If the number of possibilities is the same for
each color (here 4 e.g. {1,3,5,7}), here's another
option:

=INDEX({"BLUE","YELLOW","GREEN","RED","PINK","BLACK","BROWN"},
ROUNDUP(MATCH(x,{2,4,6,8,1,3,5,7,10,12,14,16,11,13,15,17,100,200,300,
400,500,600,700,800,45,55,65,75},0)/4,0))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

thanks--this was the most helpful by far!
But I am still encountering problems after about 7 groups (are these called
arrays?)
Anyway I tried to do this and got a Value error:
=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YELLOW",IF

(OR(x={10,12,14,16}),"GREEN",IF(OR(x={11,13,15,17}),"RED",IF(OR(x={100,200,3
00,400}),"PINK",IF(OR(x={500,600,700,800}),"black"))))))=IF(OR(x={45,55,65,7
5}),"BROWN...etc...i.e. group 3 groups of seven parenths, but not
working...