Overall Average Percent Compete
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.
Comments
-
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
-
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)
-
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.
-
Hi TKeller,
I am unfamiliar with the % Complete system column. The most parent row is Task Name 11 row.
-
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
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.
-
=(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)
-
Maybe it would help if I gave you the link to the project
https://app.smartsheet.com/b/publish?EQBCT=3fb3cc4472c441378a412fc0d57ff82e
-
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.
-
That doesn't seem to be working for my project, I think because the column I want averaged is a checked box column.
-
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.
-
That helped so much thank you!
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!