IF/AND Formula help

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
Based on what I gather so far, these are the results you want:

Book1
YZAAABACAD
12ValueValueResult
13Both 000N
14z=0, aa<00-1
15z=0,aa>001N
16aa>0,z=010Y
17aa<0,z=0-10
18z>aa21see ab/ac
19z<aa12see ab/ac
20z=aa11see ab/ac
21
22ValueValueResult
23ab<ac729Y
24ab>ac297N
25ab=ac77
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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
See if this works for you:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,650
Messages
5,626,084
Members
416,161
Latest member
David1966Lewis

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