# Overall Average Percent Compete

Options
edited 12/09/19

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.

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

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.

• Options

Hi TKeller,

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

• ✭✭✭✭✭✭
Options

Hi Marcia,

% Complete and the other project sheet columns are great.

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

=(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)

• Options

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

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

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• edited 10/31/18
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

That helped so much thank you!

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!