Having trouble with IF statement that includes AND function

chaos

Board Regular
Joined
Feb 24, 2003
Messages
119
Hi,

I'm trying to write and IF statement that also, i think, requires an AND function, and I'm having trouble.

Here's what I'm trying to say in plain English:

IF A3 is less than A2, return RED
If A3 is less than A2 and A2 is less than A1, return LIGHTRED
IF A3 is greater than A2, return Green
If A3 is greater than A2 and A2 is greater than A1, return LIGHTGREEN

Here's what I've written so far but it's not working:

=IF(A3 < A2,"RED",IF(A3 > A2,"GREEN",IF(AND(A3 < A2),(A2 < A1),"LIGHTRED",IF(AND(A3 > A2),(A2 > A1),"LIGHTGREEN")))

Any help would be appreciated.

chaos
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Hi chaos,

Try

=IF(A3 < A2,"RED",IF(AND(A3 < A2,A2 < A1),"LIGHTRED",IF(A3 > A2,"GREEN",IF(AND(A3 > A2,A2 > A1),"LIGHTGREEN"))))
 

chaos

Board Regular
Joined
Feb 24, 2003
Messages
119
Hi Jeff,

Thanks for the fast response! Your formula is returning REDs and Greens, but not LIGHTREDs OR LIGHTGREENs.

Any other thoughts on how to tackle it?

Thx,

chaos
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Looks like the AND statements should go first...

=IF(AND(A3 > A2,A2 > A1),"LIGHTGREEN",IF(AND(A3 < A2,A2 < A1),"LIGHTRED",IF(A3 < A2,"RED",IF(A3 > A2,"GREEN"))))

With the IF statements, the statement will exit out when it hits the first TRUE.
 

chaos

Board Regular
Joined
Feb 24, 2003
Messages
119
Hi Jeff,

Worked like a charm. Thanks for your help:)

chaos
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
You are most welcome. Glad it worked for you and thanks for the feedback :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

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

Which adblocker are you using?

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
Top