if...or....function
 

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

=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YEL LOW",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}),"Y ELLOW",IF
(OR(A1={10,12,14,16}),"GREEN")))


-----Original Message-----
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....

.

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(A19,"Green",IF(MOD(A1,2)=1,"Yellow","Blue"))

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Mgt" wrote in message
...
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....

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}),"YEL LOW",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...


"Bob Umlas Excel MVP" wrote:

=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YEL LOW",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}),"Y ELLOW",IF
(OR(A1={10,12,14,16}),"GREEN")))


-----Original Message-----
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....

.

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}),"YE LLOW",
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,60 0,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","")
&...

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


On Tue, 15 Mar 2005 15:25:04 -0800, Mgt wrote:

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}),"YE LLOW",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,6 5,75}),"BROWN...etc...i.e. group 3 groups of seven parenths, but not working...


"Bob Umlas Excel MVP" wrote:

=IF(OR(x={2,4,6,8}),"BLUE",IF(OR(x={1,3,5,7}),"YEL LOW",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}),"Y ELLOW",IF
(OR(A1={10,12,14,16}),"GREEN")))


-----Original Message-----
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....

.

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","BLAC K","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.

"Mgt" skrev i en meddelelse
...
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}),"YEL LOW",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...