Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Multiple If Conditions Multiple If Conditions
Register FAQ

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,962
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,962
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,962
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,962
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

« Previous Thread | Next Thread »


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 11:20 PM.

Contact Us - Privacy Statement - Top

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