AVG % that includes blank children rows

Options

I am turning off the % complete in the project sheet setup due to a milestone (Parent) section that requires manual input of the % complete. Therefore, I need to enter formulas in the % Complete column parent to calculate the % complete for the children but it needs to include the tasks that are blank. AVG(CHILDREN()) does not work. What is the best formula to use to count the number of children and the entered % complete to find the average?


Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Options

    @Dana S,

    In theory and by definition, a milestone is a task with a 0 day duration. Parent rows are not typically milestones. Have you considered adding a line(s) under your parent row to capture your milestone(s), so that you don't have to disable the % complete functionality?

    It's not just an average taken to apply % complete to parent rows. The parent row % complete is calculated by considering the duration of each of the children rows (weighted average).

    The solution you're asking about would not be scalable. You would have to apply that formula to every parent row other than that one section you refer to.

    All the best,

    -Ray

  • Dana S
    Dana S ✭✭
    Options

    That is a misnomer on my part. I do have Parents with milestone underneath. The milestones have durations, the parents do not. The Parent is the light blue row above each set of milestones. I am wanting to apply a formula to each parent row (which does not have any durations or predessors) and then leave the one parent that needs to be a manual input without any formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!