Calculating the Average of Descendant Rows

ksmith33

Hi there, I am having some trouble calculating the average of the "RYG Average" column for the child rows in my Smartsheet. I have a helper column called "Parent Row" that has a checkbox for any rows that are considered parent rows. I am wanting to calculate the RYG Average for the child rows under each parent row. Here is the formula I have been using:

=AVG(COLLECT(DESCENDANTS(@row, [Parent Row]:[Parent Row]), [RYG Average]@row))

but Smartsheet keeps saying that this is #UNPARSABLE. I am wondering if this is due to the fact that the hierarchy has multiple levels. For example, the "Security" parent row also has a few sub-departments that fall under it that are also parent rows. I want to calculate the average for "Security" as well as it's sub-functions. Any guidance you can offer would be much appreciated as I am new to Smartsheet.

Paul Newcome

Are you able to provide a screenshot for context?

ksmith33

Here is a photo for context. You can see Security at the top with several sub-departments underneath. Each sub-department has various tasks. I want to calculate the RYG Average of Security and each sub-department (SecOps, ProdSec, SA&T, and IAM, respectively).

Paul Newcome

In that case, I would suggest just putting the average on each parent row which will in turn feed up to the next level.

=AVG(CHILDREN())