Need help with "IF" function

[MT]

I need some help with the IF function, What I am trying
to do is to set the following conditions, it is like a 4Q
matrix:

- If Cell A2 is greater than 5 AND cell B2 is Greater
than 5 then answer to cell C2 is equal to RED
- If Cell A2 is greater than 5 AND cell B2 is less or
equal to 5 then answer to cell C2 is equal to ORANGE
- If Cell A2 is less or equal to 5 AND cell B2 is Greater
than 5 then answer to cell C2 is equal to YELLOW
- If Cell A2 is less or equal to 5 AND cell B2 is less or
equal to 5 then answer to cell C2 is equal to GREEN

The formula I have put in as follow but doesn't seems to
work probably, please help
=IF(A2>5&B2>5,"Red",IF(A2>5&B2<=5,"Orange",IF
(A2<=5&B2>5,"Yellow",IF(A2<=5&B2<=5,"Green"))))

Many thanks!

 

[Frank Kabel]

Hi
try
=IF(A2>5,IF(B2>5,"red","orange"),IF(B2>5,"yellow","green"))

 

[Bob Umlas]

=IF(AND(A2>5,B2>5),"Red",IF(AND(A2>5,B2<=5),"Orange",IF(AND(A2<=5,B2>5),"Yel
low",IF(And(A2<=5,B2<=5),"Green"))))

 

[Govind]

Hi,

Try this

=IF(AND(A2>5,B2>5),"Red",IF(AND(A2>5,B2<=5),"Orange",IF(AND(A2<=5,B2>=5),"Yellow",IF(AND(A2<=5,B2<=5),"Green","None"))))

Govind.

 

[Arvi Laanemets]

Create a table on another sheet, p.e. Source, in range p.e. A1:B2
Green Yellow
Orange Red

Your formula will be
=INDEX(Source!$A$1:$B$2,MIN(CEILING(A1/5,1),2),MIN(CEILING(B1/5,1),2))

 

[Govind]

Quite simple one. Frank is Great !

Govind.

 

[MT]

Frank and Govind, Thanks a lot! Both works fine.

-----Original Message-----
Quite simple one. Frank is Great !

Govind.



.

 

[Ron Rosenfeld]

I need some help with the IF function, What I am trying
to do is to set the following conditions, it is like a 4Q
matrix:

- If Cell A2 is greater than 5 AND cell B2 is Greater
than 5 then answer to cell C2 is equal to RED
- If Cell A2 is greater than 5 AND cell B2 is less or
equal to 5 then answer to cell C2 is equal to ORANGE
- If Cell A2 is less or equal to 5 AND cell B2 is Greater
than 5 then answer to cell C2 is equal to YELLOW
- If Cell A2 is less or equal to 5 AND cell B2 is less or
equal to 5 then answer to cell C2 is equal to GREEN

The formula I have put in as follow but doesn't seems to
work probably, please help
=IF(A2>5&B2>5,"Red",IF(A2>5&B2<=5,"Orange",IF
(A2<=5&B2>5,"Yellow",IF(A2<=5&B2<=5,"Green"))))

Many thanks!

=VLOOKUP(A2,{-100,"Green","Yellow";5,"Orange","Red"},MATCH(B2,{-100,5})+1)

Substitute for -100 the smallest number that could be in A2 and B2.


--ron