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?


Best Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    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.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    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
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!