Overall Average Percent Compete

Marcia Drabek

Hi All,

 

I have looked around on multiple forms and haven't been able to find a solution for why my formula isn't working.I am trying to determine the formula for the total project % complete of the children rows.

For the Project Initiation rows I have the formula =ROUND(IF(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()), 0) * 100, 0) + "%"

For the completed?11 cell I tried to use the formula =AVG(CHILDREN()) and I get a #DIVIDE BY ZERO error. 

How do I get the Completed?11 cell to show me the total percentage for the entire project? Please note this sheet is for project  templates.

TKeller

Hi Marcia, 

Have you tried using the % Complete system column? as long as your project title is the top-most parent, and everything else is children underneath, it will automatically calculate overall percentage complete. (You do need to have duration and predecessors turned on, but this helps in managing the overall project). 

 

Hope this helps

Paul Newcome

I'm going to suggest creating a hidden helper column (we'll call it "Helper" for this example).

 

In each of the parent rows of the helper column use 

=ROUND(IF(COUNTIF(CHILDREN([Complete?]@row), 1) / COUNT(CHILDREN([Complete?]@row)) > 0, COUNTIF(CHILDREN([Complete?]@row), 1) / COUNT(CHILDREN([Complete?]@row)), 0) * 100, 0)

 

This will give you the NUMBER. In the [Complete?] column, you can enter

 

=Helper@row + "%"

 

This will combine your number with the percent sign.

 

For the overall row you would enter into the Helper column

 

=ROUND(AVG(CHILDREN()), 0)

Marcia Drabek

It says that the first formula you had me enter is #Unparsable. I'm wondering if the reason that the average formula wont work because the children rows have a formula in them.

Marcia Drabek

Hi TKeller,

 

I am unfamiliar with the % Complete system column. The most parent row is Task Name 11 row.

Andrée Starå

Hi Marcia,

% Complete and the other project sheet columns are great.

Please see the attached link/screenshot for more information.

Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors

I hope this helps you!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

=(COUNTIF(CHILDREN([Complete?]@row), 1) / COUNT(CHILDREN([Complete?]@row))) * 100

 

Try the above. I took out the extras (for now) of rounding and whatnot. That should give you a number and possibly a few decimal places. If that works then you can wrap it in your ROUND function like so...

 

=ROUND((COUNTIF(CHILDREN([Complete?]@row), 1) / COUNT(CHILDREN([Complete?]@row))) * 100, 0)

Marcia Drabek

Maybe it would help if I gave you the link to the project

https://app.smartsheet.com/b/publish?EQBCT=3fb3cc4472c441378a412fc0d57ff82e

Andrée Starå

Look here at the project sheet and the %Complete on how you could use the automatic functions in Smartsheet.

https://app.smartsheet.com/b/publish?EQBCT=63979e622cc14da2a4b938194d31…

Would that work?

Best,

Andrée

Marcia Drabek

That doesn't seem to be working for my project, I think because the column I want averaged is a checked box column.

 

https://app.smartsheet.com/b/publish?EQBCT=3fb3cc4472c441378a412fc0d57ff82e&_ga=2.214611219.585918219.1540809993-150492610.1536769947

Paul Newcome

Ok. Create a text/number Helper column (called "Helper"). In that column put this formula in all of your green rows.

 

=ROUND(IF(COUNTIF(CHILDREN(Complete@row), 1) / COUNT(CHILDREN(Complete@row)) > 0, COUNTIF(CHILDREN(Complete@row), 1) / COUNT(CHILDREN(Complete@row)), 0) * 100, 0)

 

Then in the Blue row of that column, put this.

 

=ROUND(AVG(CHILDREN()), 0)

 

In your Complete column, put this in the green rows and the blue row.

 

=Helper@row + "%"

 

You can then hide your helper column to keep your sheet looking clean.

Marcia Drabek

That helped so much thank you!

Andrée Starå

Yes, that will not work because it has to be a number/text column. You'll have to do it the way Paul suggested if you must have the checkboxes.

Best,

Andrée

DMark

https://community.smartsheet.com/discussion/comment/112411#Comment_112411

Paul, I know this is an old thread but it was very helpful. I was able to figure out so many other issues I was having by dissecting this and applying what I learned to my formulas. Thank you!