Complex Multiple Condition If Then

[Aimee S.]

Here's an example of what my table looks like with labels being Columns N, O, P, & Q. Column Q is where I want my new formula with your help:

_17_ | ____ | ____ | ____
_13_ | _15_ | _15_ | ____
____ | _11_ | ____ | ____
_81_ | _52_ | ____ | ____

I need a formula in column Q that does the following:

If there is a value in Column P, disregard all other columns and pull the value in Column P into Column Q.

If there is NO value in Column P, but there IS a value in Column N and/or Column O, pull the greater of all available values in Column N and/or O into Column Q.

If no values are present in any of Columns N, O or P, make Column Q show "Eng Not Complete".

 

[lenze]

Are the values numeric?? Can they be negative? zero?

lenze

 

[Aimee S.]

The values are actually dates in dd/mm/yy format. I simplifed to numbers for the purpose of this thread. Excel as you know figures out great or less than for dates the same as numbers.

There are no zeros as to how I have set this up. Only blanks where applicable. And no negative numbers, or dates as the case may be

 

[RSXchin]

i think this'll work

=IF(AND(ISBLANK(N1),ISBLANK(O1),ISBLANK(P1)),"Eng Not Complete",IF(ISBLANK(P1),MAX(N1,O1),P1))

 

[Aimee S.]

i think this'll work

=IF(AND(ISBLANK(N1),ISBLANK(O1),ISBLANK(P1)),"Eng Not Complete",IF(ISBLANK(P1),MAX(N1,O1),P1))

Thanks, but this formula is not pulling any values whatsoever if Column P has no data and there is a value in either Column N and/or Column O. It does appear to return a blank value if all three columns are blank, or the value in Column P if one is present...

Oh, and the Eng Not Complete thing is not showing correctly if all three cells are blank..

 

[Haseeb Avarakkan]

Hello, Try

Q2, copy down...

=IF(N(N2),N2,IF(COUNT(O2:P2),MAX(O2:P2),"Eng Not Complete"))

 

[RSXchin]

did you make sure to change all the 1's to whatever row you put it in to begin with?

 

[Aladin Akyurek]

Here's an example of what my table looks like with labels being Columns N, O, P, & Q. Column Q is where I want my new formula with your help:

_17_ | ____ | ____ | ____
_13_ | _15_ | _15_ | ____
____ | _11_ | ____ | ____
_81_ | _52_ | ____ | ____

I need a formula in column Q that does the following:

If there is a value in Column P, disregard all other columns and pull the value in Column P into Column Q.

If there is NO value in Column P, but there IS a value in Column N and/or Column O, pull the greater of all available values in Column N and/or O into Column Q.

If no values are present in any of Columns N, O or P, make Column Q show "Eng Not Complete".

Try...

Q2:

=IF(ISNUMBER(P2),P2,IF(COUNT(N2,O2),MAX(N2,O2),"Eng Not Complete"))

 

[Aimee S.]

Both Aladin and Haseeb's solutions work. Very elegant! Thanks!

So to wrap up my day, I would need to just grab this in formula form:

If column V contains a value, and column Q shows "Eng Not Complete", make column R show "CCRD But No EP".
If however column V does NOT contain a value, simply pull value from Column Q into Column R.

 

[Aladin Akyurek]

Both Aladin and Haseeb's solutions work. Very elegant! Thanks!

Great. Thanks for providing feedback.

So to wrap up my day, I would need to just grab this in formula form:

If column V contains a value, and column Q shows "Eng Not Complete", make column R show "CCRD But No EP".
If however column V does NOT contain a value, simply pull value from Column Q into Column R.

Looks like...

R2:

=IF(V2<>"",
      IF(Q2="Eng Not Complete","CCRD But No EP",""),
      Q2)