Average % Complete of CHILDREN TASKS

gdooley@jbelectric

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?

David Tutwiler

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.

David Tutwiler

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.

David Tutwiler

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

David Tutwiler

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.

gdooley@jbelectric

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

David Tutwiler

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.

Andrée Starå

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!

gdooley@jbelectric

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.

David Tutwiler

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?

gdooley@jbelectric

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

David Tutwiler

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

Andrée Starå

@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)

gdooley@jbelectric

That still does not work.

Andrée Starå

@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?

David Tutwiler

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

gdooley@jbelectric

David and Andree,

Thank you sooooooo much that has worked!!!!

Andrée Starå

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

Andrée Starå

@David Tutwiler

Thanks! Easy to miss!