+ Reply to Thread
Results 1 to 10 of 10

Percentage Completed Reference...

  1. 12-13-2011, 11:57 PM #1
    DMA-Pacific
    DMA-Pacific is offline
    Registered User
    Join Date
    11-18-2011
    Location
    Colorado Springs, Colorado
    MS-Off Ver
    Excel 2016
    Posts
    17

    Percentage Completed Reference...

    I'm in DESPERATE need to figure this out. Hopefully you can see the pic I've inserted. In the document you can see the check boxes in column B. I want the percentage at the bottom of each milestone to tally the percentage of task completion based on the number of tasks....it keeps tallying the empty spaces... someone gave me a formula that ignores the empty spaces however, when only one box is checked it says 200%... not what I'm looking for. Can someone please help me? This is a google doc as well, so if you need access to help me out, I can do that and see if that works. Thanks so much for you smart people who can help us technically deficient folks out.



    Excel Problem.jpg
    Last edited by DMA-Pacific; 12-21-2011 at 02:50 AM.
    Reply With QuoteRegister To Reply
  2. 12-15-2011, 08:24 PM #2
    rscsmith
    rscsmith is offline
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Percentage Completed Reference...

    Hi there

    Here is one possible solution:
    Please Login or Register  to view this content.
    Example attached. If this doesn't work, please post an example of your sheet and I'll try again. It is generally better to post an example sheet where possible, rather than just a picture, so we can test our theories on your data before responding.

    Best regards, Rob.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 12-15-2011, 11:57 PM #3
    DMA-Pacific
    DMA-Pacific is offline
    Registered User
    Join Date
    11-18-2011
    Location
    Colorado Springs, Colorado
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Percentage Completed Reference...

    rscsmith, you're a genius... I had just given up on ever getting this figured out. THANKS SO MUCH... you really made this work for me. I had one more question... I'm averaging the percentages and when there's no data in the percentage fields it makes a #DIV/0 error... how can I have it ignore that?

    Thanks again for all your time and attention... you're my hero for the day ...lol.
    Reply With QuoteRegister To Reply
  4. 12-18-2011, 09:56 PM #4
    rscsmith
    rscsmith is offline
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Percentage Completed Reference...

    Hi DMA

    The ISERROR function should help. 

    Please Login or Register  to view this content.
    This will return a 0 if your AVERAGE command causes an error.

    Another alternative is to COUNTA first, and if the COUNTA=0 then return 0, otherwise do the AVERAGE command:
    Please Login or Register  to view this content.
    Cheers, Rob.
    Reply With QuoteRegister To Reply
  5. 12-19-2011, 03:36 AM #5
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Percentage Completed Reference...

    To ignore the errors and average the remaining values 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 12-19-2011, 07:56 PM #6
    DMA-Pacific
    DMA-Pacific is offline
    Registered User
    Join Date
    11-18-2011
    Location
    Colorado Springs, Colorado
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Percentage Completed Reference...

    Rob, the top formula you posted did return a 0% value however, now the cell doesn't calculate the percentage when the boxes are checked... it just stays at 0%... what am I doing wrong. I've attached the doc so you can look at it and see what I'm doing wrong.

    Again, thanks for your time and attention.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 12-20-2011, 02:28 AM #7
    ghurhu
    ghurhu is offline
    Registered User
    Join Date
    12-19-2011
    Location
    Cluj, Romania
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Percentage Completed Reference...

    For the #DIV/0 problem - I guess the easiest solution is the IFERROR function:
    Please Login or Register  to view this content.
    If you want to leave the cell blank - replace 0 with "".
    Last edited by ghurhu; 12-20-2011 at 02:30 AM.
    Reply With QuoteRegister To Reply
  8. 12-20-2011, 11:01 PM #8
    rscsmith
    rscsmith is offline
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Percentage Completed Reference...

    Hi DMA

    In your attachment, change the percentage calculation for each Milestone to include an IF statement:
    Please Login or Register  to view this content.
    This will avoid #DIV/0 for empty milestones.

    Text values aren't considered when averaging, so now you can change your Total Project Completion Percentage formula to:
    Please Login or Register  to view this content.
    (You need to trap the SUM=0 to avoid a #DIV/0 error when you haven't checked any milestones.)

    This sorted it in Excel 2003, so hopefully it will work for you. :-)

    Best regards, Rob.
    Last edited by rscsmith; 12-20-2011 at 11:06 PM.
    Reply With QuoteRegister To Reply
  9. 12-21-2011, 02:47 AM #9
    DMA-Pacific
    DMA-Pacific is offline
    Registered User
    Join Date
    11-18-2011
    Location
    Colorado Springs, Colorado
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Percentage Completed Reference...

    Rob, it didn't work with the "-" reference... it didn't like ignoring the text, but I changed it to "0" and it worked great. Thanks so much for sticking with me on this... If I get a promotion you most certainly earned a cut ... thanks again and I appreciate all your help.
    Reply With QuoteRegister To Reply
  10. 12-21-2011, 04:27 AM #10
    rscsmith
    rscsmith is offline
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Percentage Completed Reference...

    You're welcome.

    Strange it didn't work for you. I've just tried it in Excel 2010 and it worked ok, and it worked in my 2003 version.

    You may need to check your averages if you have milestones with no activities, and the completion is returning 0, may change the average (downwards). Did you put inverted commas around the 0? i.e.:
    Please Login or Register  to view this content.
    But "if it ain't broke, don't fix it"!

    Cheers, Rob.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1