#1
|
|||
|
|||
Multiple If Conditions
I am having a problem with this formula. =IF(F2="No","Delivery date needed",IF(F2="Yes",IF(C2<>"","Services",IF(D2="NO ",IF(B2>$H$1,"Schedule not due yet",IF(F2="Yes",IF(C2="",IF(D2="NO",IF(B2<H$1,IF( E2="","Missing",IF(F2="Yes",IF(C2="",IF(D2="Yes",I F(B2>=$H$1,IF(E2>=B2,"Proactive")))))))))))))))
Everything working except the last condition. "Proactive" |
#2
|
||||
|
||||
It seems to me your formula could be reduced to something like:
Code:
=IF(B2+C2=0,"Delivery date needed",IF(C2>0,"Services",IF(AND(D2="NO",B2>$H$1),"Schedule not due yet",IF(D2="NO",IF(AND(B2<H$1,E2=0),"Missing",IF(D2="YES",IF(AND(B2>=$H$1,E2>=B2),"Proactive",""),"")),"")))) Code:
=IF(F2="No","Delivery date needed",IF(F2="Yes",IF(C2<>"","Services",IF(D2="NO",IF(B2>$H$1,"Schedule not due yet",IF(F2="Yes",IF(C2="",IF(D2="NO",IF(B2<H$1,IF(E2="","Missing",IF(F2="Yes",IF(C2="",IF(D2="Yes",IF(B2>=$H$1,IF(E2>=B2,"Proactive",""),""),""),""),"")),""),""),""),"")),"")),""))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks for cleaning up the formula, but I still is having an issue with it. It returns a blank cell, instead of "Proactive".
Z941714 |
#4
|
||||
|
||||
As I said, and as the formula I posted indicates, you have a number of conditions not catered for. I don't know what your expectations are for those conditions.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Paul,
I think I got all of the conditions catered for. Check this formula =IF(B2+C2=0,"Delivery date needed",IF(C2>0,"Services",IF(AND(D2="NO",B2>$H$1) ,"Schedule not due yet",IF(D2="NO",IF(AND(B2<H$1,E2=0),"Missing",IF(D 2="YES",IF(AND(B2>=$H$1,E2>=$H$1),"Proactive",IF(D 2="YES",IF(AND(B2<$H$1,E2>=$H$1),"Current",IF(D2=" YES",IF(AND(B2<$H$1,E2<$H$1),"Expired"))))))))))) When D2="YES", it returns "FALSE" Any suggestions? |
#6
|
||||
|
||||
I disagree.
So far, you haven't catered for all possibilities if D2="NO". Under that, you've provided for: • B2>$H$1 • B2<H$1,E2=0 but what about: • B2=$H$1 • B2<H$1,E2<>0 Likewise for D2="YES", you've provided for: • B2>=$H$1,E2>=$H$1 • B2<$H$1,E2>=$H$1 • B2<$H$1,E2<$H$1 but what about: • B2>=$H$1,E2<$H$1 You also haven't catered for D2="". Your latest formula can be simplified to: Code:
=IF(B2+C2=0,"Delivery date needed",IF(C2>0,"Services",IF(D2="NO",IF(B2>$H$1,"Schedule not due yet",IF(AND(B2<H$1,E2=0),"Missing","")),IF(D2="YES",IF(AND(B2>=$H$1,E2>=$H$1),"Proactive",IF(AND(B2<$H$1,E2>=$H$1),"Current",IF(AND(B2<$H$1,E2<$H$1),"Expired",""))),""))))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
B2=$H$1,"Due today"
B2<$H$1,E2<>0, E2<>0 is "Missing" when there a date in E2 if it is greater the $H$1,"Current", if less than "Expired" B2>=$H$1,E2<$H$1, I need to think on this. D2="" D will always have a yes or no, system report.(I should stated before) |
#8
|
|||
|
|||
B2>$H$1,E2<$H$1, "Expired"
Paul, You have been a great help, I think this is everything Thanks Z941417 |
#9
|
||||
|
||||
Since that's the same result as for B2<$H$1,E2<$H$1, you can reduce the 'yes' test to:
Code:
IF(D2="YES",IF(AND(B2>=$H$1,E2>=$H$1),"Proactive",IF(E2>=$H$1,"Current","Expired")) Quote:
Code:
=IF(B2+C2=0,IF(D2="","","Delivery date needed"),IF(C2>0,"Services",IF(D2="NO",IF(B2=$H$1,"Due today",IF(B2>$H$1,"Schedule not due yet",IF(E2=0,"Missing",IF(E2>H$1,"Current","Expired")))),IF(D2="YES",IF(AND(B2>=$H$1,E2>=$H$1),"Proactive",IF(E2>=$H$1,"Current","Expired")),"Error"))))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
multiple conditions, and blank cells with the countifs function | jaden0605 | Excel | 1 | 03-30-2014 01:50 AM |
6 conditions formula | paconovellino | Excel | 2 | 03-05-2012 06:45 AM |
3 Conditions | Laurie B. | Excel | 4 | 08-15-2011 10:27 PM |
Count with multiple conditions | bundy5150 | Excel | 4 | 02-22-2011 10:00 AM |
Using Conditions to Add Values | EclipticalD | Excel | 1 | 07-08-2010 09:50 PM |