Sum child rows that use a formula?

I have child rows that use a formula to calculate % completion based on input to another column. I'd like the parent row to return a value based on the child values (Specifically, sum of the child rows divided by the number of child rows).

As the child rows use a formula rather than a direct number entry, entering a =SUM(CHILDREN()) / COUNT(CHILDREN()) formula in the parent row doesn't work.

I'm not sure how to manage the data to get the result I'm seeking. Thanks for any thoughts you have!

Tags:

Best Answer

  • S Richardson
    S Richardson ✭✭
    Answer ✓

    Solved: I needed to change the value output of the formula in the child rows, and now the parent row AVG formula works as expected.

    =IF(Progress@row = "Complete", 1, IF(Progress@row = "In Progress", .25, IF(Progress@row = "Not Started", 0, IF(Progress@row = "On Hold", .25))))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    =IFERROR(AVG(CHILDREN()), "")

    This should do it, but you cannot set it up as a column formula without additional information.

  • Using that formula results in just a blank, unfortunately. It won't be a column formula, as there's other formulas in different rows within the same column.

    I tried the formula =AVG(CHILDREN()) as well, which resulted in an error #DIVIDE BY ZERO

    To provide a bit more detail:

    I have five rows that use the formula =IF(Progress@row = "Complete", "100%", IF(Progress@row = "In Progress", "25%", IF(Progress@row = "Not Started", "0%", IF(Progress@row = "On Hold", "25%"))))

    I want the parent row to average the returned values from those five child rows.

  • S Richardson
    S Richardson ✭✭
    Answer ✓

    Solved: I needed to change the value output of the formula in the child rows, and now the parent row AVG formula works as expected.

    =IF(Progress@row = "Complete", 1, IF(Progress@row = "In Progress", .25, IF(Progress@row = "Not Started", 0, IF(Progress@row = "On Hold", .25))))

  • Marcela
    Marcela Employee

    Hey @S Richardson, Thank you for sharing your solution!

    Join us at Smartsheet ENGAGE 2024🎉

    October 8 - 10, Seattle, WA | Register now

  • ssummer
    ssummer ✭✭

    @S Richardson - thank you for all of this! made it much easier than all of the other advice I was trying out!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!