Average % Complete of CHILDREN TASKS

This is because you are dropping in text into your cells. When you say "0%" that is dropping the text characters for "0%" not 0.00. I think if you changed your formula to say 0.25 for 25%, 0.50 for 50%, and then changed the format of your column to display % (should be a button in the toolbar that you can click to do that) then your averages will start working.

Shorter explanation, I think your current system is trying to average text values when you really mean to average percentages.

Please try just doing

=AVG(CHILDREN())

Adding the 0 is adding 0 into the AVG of all your other cells. You just want to AVG the children of your parent row.

Also, to change the way that looks, click on the title of the PERCENT COMPLETE column and select the % tool in the tool bar to make the whole column show as percentages instead of decimals.

Good catch @Andrée Starå . Totally missed that the "" were still there on the second run.

All comments

Shorter explanation, I think your current system is trying to average text values when you really mean to average percentages.

This did not work. I removed the % from the Children formula and change the format on the column to display % and it does not do that. Now the Percent Complete just shows the decimal without the percent sign. The formula that I added in the Parent row was AVG(CHILDREN(),0) and the value that shows up is 0%.

CHILDREN row formula

PARENT row formula

Please try just doing

=AVG(CHILDREN())

Adding the 0 is adding 0 into the AVG of all your other cells. You just want to AVG the children of your parent row.

Also, to change the way that looks, click on the title of the PERCENT COMPLETE column and select the % tool in the tool bar to make the whole column show as percentages instead of decimals.

Hi @gdooley@jbelectric

I hope you're well and safe!

Try something like this.

=IF(COUNTIFS(PIPED@row:DONE@row, true) = 0, 0, IF(COUNTIFS(PIPED@row:DONE@row, true) = 1, 0.25, IF(COUNTIFS(PIPED@row:DONE@row, true) = 2, 0.50, IF(COUNTIFS(PIPED@row:DONE@row, true) = 3, 0.75, IF(COUNTIFS(PIPED@row:DONE@row, true) = 4, 0.99, IF(COUNTIFS(PIPED@row:DONE@row, true) = 5, 1)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andree,

Your advise above did not work, I got an "UNPARSEABLE" error when I changed it.

David,

When I put your formula in the parent row to get the Average percent complete of the children rows I get #DIVIDE BY ZERO error.

Can you please update with another screenshot that could potentially show all of the children under the parent so we can see the whole list?

That is all the children under that particular parent. I have 6 other parent rows that I want to use the same formula in. I am currently only working on the first Parent Row hoping that I get that formula correct and than can work my way down the sheet.

You need to know that when a box is checked, and it does not matter which box is checked it will increase the percent complete in the Children Row. Not sure if that matters...

@Paul Newcome Any ideas? The AVG(CHILDREN()) should work.

@gdooley@jbelectric

Strange!

Firstly, as David mentioned, you'd need to change the numbers "25" to 0.25 and similar.

Smartsheet looks at the numbers in a column formatted for percentages as values between 0 and 1. You'd need to use decimal values instead for it to work

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1

Depending on your country/region, you'll need to exchange the comma for a period.

Secondly, try the above and let us know if that works or not.

I'd be happy to take a quick look if it doesn't.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

That still does not work.

@gdooley@jbelectric

You need to remove the " " surrounding the 0.25 and the other values because otherwise, Smartsheet reads it as text, and that's probably why it's not working.

Make sense?

Good catch @Andrée Starå . Totally missed that the "" were still there on the second run.

David and Andree,

Thank you sooooooo much that has worked!!!!

@gdooley@jbelectric

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!