IF/AND Formula help

[James8761]

Hi,

Can someone help me finish the below formula please. I am trying to put the formula in to Cell Y5. I

In Cell Z5 the number is 2, Cell AA5 the number is 0. So the correct answer to the formula would be Y. This only works if Z5 is higher than 0 and Cell AA5 is exactly zero.
In Cell Z6 the number is 0, Cell AA6 is number 0. So the correct answer to the formula would be N.
In Cell Z7 the number is 1, Cell AA7 is number 2. I would then want to look at Column AB7 and Cell AC7. The number in Cell AB7 is 7 and the number in Cell AC7 is 29. As the number in Cell AB7 is lower than AC7 I would like the answer to the formula to be Y.
In Cell Z8 the number is 1, Cell AA8 is number 1. I would then want to look at Column AB8 and Cell AC8. The number in Cell AB7 is 27 and the number in Cell AC7 is 9. As the number in Cell AC8 is lower than AB8 I would like the answer to the formula to be N.
In Cell Z9 the number is 3, Cell AA9 is number 2. I would then want to look at Column AB9 and Cell AC9. The number in Cell AB9 is 17 and the number in Cell AC9 is 9. As the number in Cell AC9 is lower than AB9 I would like the answer to the formula to be N.

Sorry for the long text, it's pretty late!

Thanks for any help!

 

[Eric W]

I believe you left out some possible cases, but this formula gives the results you ask for:

=IF(AND(Z5>0,AA5=0),"Y",IF(AND(Z5=0,AA5=0),"N",IF(AB5<AC5,"Y","N")))

 

[James8761]

I believe you left out some possible cases, but this formula gives the results you ask for:

=IF(AND(Z5>0,AA5=0),"Y",IF(AND(Z5=0,AA5=0),"N",IF(AB5<AC5,"Y","N")))

Thanks Eric. I think you are right I may have missed a bit out. Your formula works great on most of the data.
However, in Cell Z23 the number is 0 and Cell AA23 is 2 so I would like the answer to be N, currently it is a Y.

Thanks for any further input.

 

[Eric W]

Based on what I gather so far, these are the results you want:

Book1
	Y	Z	AA	AB	AC	AD
12		Value	Value	Result
13	Both 0	0	0	N
14	z=0, aa<0	0	-1
15	z=0,aa>0	0	1	N
16	aa>0,z=0	1	0	Y
17	aa<0,z=0	-1	0
18	z>aa	2	1	see ab/ac
19	z<aa	1	2	see ab/ac
20	z=aa	1	1	see ab/ac
21
22				Value	Value	Result
23			ab<ac	7	29	Y
24			ab>ac	29	7	N
25			ab=ac	7	7
Sheet6

There are 2 cases on rows 14 and 17 that I'm not sure of, plus the case on row 25 with the ab/ac columns. Can you verify that these are the results you want, and fill in the missing values?

 

[James8761]

Hi Eric,
In the case of Rows 14 and 17 the minimum number is 0, as there can be no negative numbers.
In Row 25 there will always be a minimum difference of 1, so the two numbers couldn't be the same.

Hope that makes sense. Thanks again!

 

[Eric W]

See if this works for you:

=IF(Z5=0,"N",IF(AA5=0,"Y",IF(AB5<AC5,"Y","N")))

 

[James8761]

See if this works for you:

=IF(Z5=0,"N",IF(AA5=0,"Y",IF(AB5<AC5,"Y","N")))

Thanks very much Eric, seems to work just fine.
Thanks for your patience!

 

[Eric W]

Happy to help!