IF / AND argument

[smck123]

Can anyone tell me what is wrong with the following formula? =IF(AS26<0,"G",IF(AS26>0<>AS26<5,"Y",IF(AS26>6,"R"))). The target cell is AV14. I want the target cell to equal one of three values (G,Y,R) given that the value of AS26 meets the following requirements: If AS26 is <0 = "G", If AS26 is >0 but <5 = "Y" and If AS26 is >6 = "R"... HELP!

 

[Chris Mack]

=IF(AS26<0,"G",IF(AND(AS26>0,AS26<5),"Y",IF(AS26>6,"R")))

Hope this helps,

Chris.

 

[Special-K99]

If the value of AS26 is BETWEEN 5 and 6 the result will be blank, is that what you want?

Try this instead

=LOOKUP(AS26,{-1E100,0,5,6},{"G","Y","Y","R"})

 

[smck123]

Chris - The value of >6 does not result in an "R" with your formula

 

[smck123]

Special K99 - I'm looking for a "Y" value in that case. This for a variance report. If the variance value is below 0 ("G") if it is between 1 and 5 ("Y") and over 6 ("R")

 

[Chris Mack]

Chris - The value of >6 does not result in an "R" with your formula

It does for me.

 

[smck123]

It does for me.

Hmm - It results in a "Y" for me.... Excel 2010

 

[smck123]

Hmm - It results in a "Y" for me.... Excel 2010

I see what I did wrong.... The format is %. Once I added % to your formula, it works. Thanks for your input!

 

[Chris Mack]

Np!