+ Reply to Thread
Results 1 to 4 of 4

Calculating percentage of completed tasks in various rows

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    Honoulu, Hi
    MS-Off Ver
    Excel 2010
    Posts
    12

    Calculating percentage of completed tasks in various rows

    Hello,

    I need help with calculating completed tasks that appear on random rows. I'm already using the formula =COUNTA(XX:XX)/COUNTA(XX:XX) but need it to automatically recognize which range of cells to apply the formula to. In the attached file, the projects are bolded and color filled. Column G calculates the non-bold/non-color filled cells below it that are completed which is indicated by an "X". The number of tasks below each project varies and currently we need to copy the formula and manually indicate which cells to calculate. Is there a formula or VBA code that can determine which of the following cells to calculate based on color fill or bold font? Any other ideas to make this work simpler without having to change the cell range for each project? I hope I explained my question well enough.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Calculating percentage of completed tasks in various rows

    HI excelnoob808,


    See the attached file where I have used below formula;-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where xxx and data are defined names. Press ctrl + F3 to see the logic behind define names.

    Sample Project Plan.xlsx
    The formula is taking help of a supporting column A. see if you can add a word "task" in your original tasks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    Honoulu, Hi
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Calculating percentage of completed tasks in various rows

    Hi DILIPandey,

    Thanks for your response. The tasks listed will have various different names. The file i provided was just a sample to use. How would i implement your solution if that were the case?

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Calculating percentage of completed tasks in various rows

    To apply this solution, you need to define the name as I have shown in above example.

    Press Ctrl + F3 to open name manager and apply the formula in your workbook using above sample. Thanks.



    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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