# IF / AND argument

#### smck123

##### New Member
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!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Code:
``=IF(AS26<0,"G",IF(AND(AS26>0,AS26<5),"Y",IF(AS26>6,"R")))``
Hope this helps,

Chris.

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"})

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

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 - The value of >6 does not result in an "R" with your formula
It does for me.

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!

Np!

Replies
9
Views
377
Replies
22
Views
667
Replies
3
Views
124
Replies
17
Views
326
Replies
1
Views
196

Threads
1,206,711
Messages
6,074,478
Members
446,071
Latest member
gaborfreeman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back