Having trouble with IF statement that includes AND function

[chaos]

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

 

[jeffreybrown]

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]

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]

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]

Hi Jeff,

Worked like a charm. Thanks for your help

chaos

 

[jeffreybrown]

You are most welcome. Glad it worked for you and thanks for the feedback