IF AND troubles

**theatricalveggie** · 02-05-2013, 04:37 PM

I can't figure out what I am doing wrong. Please help.

• IF K27 is equal to 0, then ELEPHANT,
• IF K27 is greater than 0, then LION
• IF K27 is greater than 0 and E45 is greater than 0, then TIGER

=IF(K27=0, “ELEPHANT”, IF(K27>0, “LION”, IF(AND(K27>0, E45>0), “TIGER”))))

Why oh why doesn't this work? THANK YOU!

**Andrew-R** · 02-05-2013, 04:39 PM

Your first two criteria have the same condition - which do you want returned; elephant or lion?

Also the third criteria will only be evaluated if the 1st 2 are false, but it also needs to be true in order for tiger to be returned.

**Special-K** · 02-05-2013, 04:44 PM

1) =IF(K27=0, “ELEPHANT”,
2) IF(K27 > 0, “LION”,
3) IF(AND(K27>0, E45>0), “TIGER”))))

3) will only get executed if 2) is FALSE
but if 2) is FALSE then your AND will not work since the K27 > 0 part of 3) will evaluate to FALSE

Try this

=IF(K27=0, “ELEPHANT”, IF(AND(K27>0, E45>0), “TIGER”,IF(K27>0, "LION","OTHER")))))))

**theatricalveggie** · 02-05-2013, 04:58 PM

1 and 2 shouldn't be the same. If K27 is 0, then I want to see ELEPHANT. If it's greater than 0, then I want to see LION.

Special-K, I pasted your formula in, but it read "The Formula you typed contains an error."

**Andrew-R** · 02-05-2013, 05:05 PM

Special-K's formula has a few too many close brackets.

=IF(K27=0, "ELEPHANT", IF(AND(K27>0, E45>0), “TIGER”,IF(K27>0, "LION","OTHER")))

PS Apologies for misreading your requirements - it's been a long day

**Special-K** · 02-05-2013, 05:07 PM

That'll teach me not to check my answers. Try this

=IF(K27=0,"ELEPHANT",IF(AND(K27>0,E45>0),"TIGER",IF(K27>0,"LION","OTHER")))

**theatricalveggie** · 02-05-2013, 05:09 PM

Special-K, you are my hero!!!!

**FDibbins** · 02-05-2013, 05:09 PM

under what conditions do you want to see TIGER?
K27=0, “ELEPHANT”
K27>0, “LION”
...(AND(K27>0, E45>0), “TIGER”))))...this will always be supersceded by K27>0, “LION”, becasue both test for K27>0, and K27>0, “LION” comes 1st, so that criteria is met before the 2nd criteria