+ Reply to Thread
Results 1 to 5 of 5

Trouble with a complex, multi-conditional % Complete formula

  1. #1
    Registered User
    Join Date
    08-02-2011
    Location
    Madison, USA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Unhappy Trouble with a complex, multi-conditional % Complete formula

    This one is a doozy and I am a bit at a loss. Any help as to a single formula solution that results in a % Complete for each row would be so incredibly helpful!

    Fact 1: For every row, Column G will have values "ISP & OSP - PRC" or "OSP Only - PRC" or "ISP Only - PRC"

    Fact 2: If a row has a value of "ISP & OSP - PRC" in Column G, relevant columns that may have project milestone dates are: J, M, V, Y, AB, AE, AH, AK, AN, AQ, & AT

    Fact 3: If a row has a value of "OSP Only - PRC" in Column G, relevant columns that may have project milestone dates are: J, M, Y, AE, AK, & AT

    Fact 4: If a row has a value of "ISP Only - PRC" in Column G, relevant columns that may have project milestone dates are: J, M, V, AB, AH, AK, AN, AQ, & AT

    Based on whichever of the three possible project type values that appears in Column G, I need a nested "OR" formula or something to that effect that will: count however many non-blank values appear in the appropriate columns for that project type (see Facts 2 through 4 above), and divide that by the total # of possible columns for that project type to get a % Project Complete figure.

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Trouble with a complex, multi-conditional % Complete formula

    attach an example pls.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Trouble with a complex, multi-conditional % Complete formula

    THis is a shot in the dark...
    =if(G2="ISP & OSP - PRC",counta(J2,M2,V2,Y2,AB2,AE2,AH2,AK2,AN2,AQ2,AT2)/11,if(G2="OSP Only",counta(J2,M2,Y2,AE2,AK2,AT2)/6,counta(J2,M2,V2,AB2,AH2,AK2,AN2,AQ2,AT2)/9))

    (could probably shorten the list by combining the common items, but it wont make that much difference)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-02-2011
    Location
    Madison, USA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Trouble with a complex, multi-conditional % Complete formula

    Awesome. I modified it ever so slightly but COUNTA was the right solution to use within a nested IF formula. Thank you so much.

    =IF(LEFT($F6,5)="isp &",COUNTA($J6,$M6,$V6,$Y6,$AB6,$AE6,$AH6,$AK6,$AN6,$AQ6,$AT6)/11,IF(LEFT($F6,5)="isp o",COUNTA($J6,$M6,$V6,$AB6,$AH6,$AK6,$AN6,$AQ6,$AT6)/9,COUNTA($J6,$M6,$Y6,$AE6,$AK6,$AT6)/6))

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Trouble with a complex, multi-conditional % Complete formula

    Glad I was able to help you, and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-29-2012, 03:21 AM
  2. Complex multi criteria lookup and count formula???
    By JapanDave in forum Excel General
    Replies: 9
    Last Post: 05-25-2009, 04:36 AM
  3. having trouble with percentage complete table
    By TTej in forum Excel General
    Replies: 1
    Last Post: 05-07-2008, 01:11 PM
  4. [SOLVED] How do I create a conditional formula that hides a complete row o.
    By Tom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2006, 07:50 AM
  5. Replies: 6
    Last Post: 03-12-2006, 06:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1