Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-07-2015, 10:08 PM
z941714 z941714 is offline Multiple If Conditions Windows 8 Multiple If Conditions Office 2007
Novice
Multiple If Conditions
 
Join Date: Aug 2015
Posts: 6
z941714 is on a distinguished road
Default 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"
Attached Files
File Type: xlsx Delivery Schedule Report ver 2.xlsx (495.7 KB, 10 views)
Reply With Quote
  #2  
Old 08-07-2015, 10:37 PM
macropod's Avatar
macropod macropod is offline Multiple If Conditions Windows 7 64bit Multiple If Conditions Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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",""),"")),""))))
Note where I have inserted "". Those indicate missing arguments from your calculations. By comparison, the missing arguments in your existing formula are as indicated by "" in:
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]
Reply With Quote
  #3  
Old 08-08-2015, 05:17 AM
z941714 z941714 is offline Multiple If Conditions Windows 8 Multiple If Conditions Office 2007
Novice
Multiple If Conditions
 
Join Date: Aug 2015
Posts: 6
z941714 is on a distinguished road
Default

Thanks for cleaning up the formula, but I still is having an issue with it. It returns a blank cell, instead of "Proactive".

Z941714
Reply With Quote
  #4  
Old 08-08-2015, 07:05 AM
macropod's Avatar
macropod macropod is offline Multiple If Conditions Windows 7 64bit Multiple If Conditions Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 08-09-2015, 05:23 AM
z941714 z941714 is offline Multiple If Conditions Windows 8 Multiple If Conditions Office 2007
Novice
Multiple If Conditions
 
Join Date: Aug 2015
Posts: 6
z941714 is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 08-09-2015, 06:35 AM
macropod's Avatar
macropod macropod is offline Multiple If Conditions Windows 7 64bit Multiple If Conditions Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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",""))),""))))
the ',""' parts are where you need to sort out the rules for the missing conditions.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 08-09-2015, 08:08 AM
z941714 z941714 is offline Multiple If Conditions Windows 8 Multiple If Conditions Office 2007
Novice
Multiple If Conditions
 
Join Date: Aug 2015
Posts: 6
z941714 is on a distinguished road
Default

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)
Reply With Quote
  #8  
Old 08-09-2015, 10:30 AM
z941714 z941714 is offline Multiple If Conditions Windows 8 Multiple If Conditions Office 2007
Novice
Multiple If Conditions
 
Join Date: Aug 2015
Posts: 6
z941714 is on a distinguished road
Default

B2>$H$1,E2<$H$1, "Expired"

Paul,

You have been a great help, I think this is everything

Thanks
Z941417
Reply With Quote
  #9  
Old 08-09-2015, 04:12 PM
macropod's Avatar
macropod macropod is offline Multiple If Conditions Windows 7 64bit Multiple If Conditions Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by z941714 View Post
B2>$H$1,E2<$H$1, "Expired"
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:
Originally Posted by z941714 View Post
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"
is ambiguous, but it seems to suggest your overall formula could be expressed as:
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple If Conditions 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
Multiple If Conditions 3 Conditions Laurie B. Excel 4 08-15-2011 10:27 PM
Multiple If Conditions Count with multiple conditions bundy5150 Excel 4 02-22-2011 10:00 AM
Multiple If Conditions Using Conditions to Add Values EclipticalD Excel 1 07-08-2010 09:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft