# Average % Complete of CHILDREN TASKS

Options

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?

• Overachievers Alumni
Options

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.

• Overachievers Alumni
Options

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

• Overachievers Alumni
Options

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

• Overachievers Alumni
Options

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.

• Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭✭✭
Options

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

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.

• Overachievers Alumni
Options

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?

• Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭✭✭
edited 10/04/22
Options

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)

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

That still does not work.

• ✭✭✭✭✭✭
edited 10/04/22
Options

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

• Overachievers Alumni
Options

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

• Options

David and Andree,

Thank you sooooooo much that has worked!!!!

• ✭✭✭✭✭✭
Options

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

Thanks! Easy to miss!

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!