Average % Complete of CHILDREN TASKS
I am trying to get the average % complete in the parent row for all the children.
Below you will see a snap shot of the work being completed, as each task is completed the PERCENT COMPLETE column will increase based on the formula in the children rows. Below is the formula in the children rows.
=IF(COUNTIFS(PIPED@row:DONE@row, true) = 0, "0%", IF(COUNTIFS(PIPED@row:DONE@row, true) = 1, "25%", IF(COUNTIFS(PIPED@row:DONE@row, true) = 2, "50%", IF(COUNTIFS(PIPED@row:DONE@row, true) = 3, "75%", IF(COUNTIFS(PIPED@row:DONE@row, true) = 4, "99%", IF(COUNTIFS(PIPED@row:DONE@row, true) = 5, "100%"))))))
Now I want to find the average % complete for all the children rows and place that in the parent rorw. I tried =AVG(CHILDREN()@row) and I get UNPARSEABLE. If I use formula =AVG(CHILDREN()) I get #DIVIDE BY ZERO. When I put a ZERO in the formula the average comes out to zero.
Can you help?
Best Answers

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.
Answers

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.

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.

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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.

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, [email protected])
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

That still does not work.

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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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!!!!

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Thanks! Easy to miss!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!