Complex Multiple Condition If Then

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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".

:eek:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Are the values numeric?? Can they be negative? zero?

lenze
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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

Well-known Member
Joined
Oct 23, 2010
Messages
758
i think this'll work
Code:
=IF(AND(ISBLANK(N1),ISBLANK(O1),ISBLANK(P1)),"Eng Not Complete",IF(ISBLANK(P1),MAX(N1,O1),P1))
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227

ADVERTISEMENT

i think this'll work
Code:
=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.. :(
 
Last edited:

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello, Try

Q2, copy down...

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

RSXchin

Well-known Member
Joined
Oct 23, 2010
Messages
758

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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".

:eek:
Try...

Q2:

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

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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:
Code:
=IF(V2<>"",
      IF(Q2="Eng Not Complete","CCRD But No EP",""),
      Q2)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top