Looking to Avg a Parent with 2 columns of averages

Bruce Johnson

Hi I am looking to average in the E%Allocation column when the level column = 3 for the averages in columns ChildAlloc1 and ChildAlloc2. Any suggestions is greatly appreciated.

jmyzk_cloudsmart_jp

Hi @Bruce Johnson

Using the DESCENDANTS function may work.

=IF(Level@row = 3, IFERROR(AVG(DESCENDANTS([%Allocation]@row)), ""))

This gives the average of all the descendants, so if you want to calculate the average of only the bottom task or the children=0 task, you may want to add a helper column, like the one on the far right in the image below, and average the descendants.

=IFERROR(AVG(DESCENDANTS([Children = 0 %Allocation]@row)), "")

https://app.smartsheet.com/b/publish?EQBCT=6f7c00d17ed34a869415c26bdb587421

Otherwise, if someone inputs a %Allocation value into a cell that is not children=0, as shown in the yellow cell in the image above, you get a different average.

jmyzk_cloudsmart_jp

Hi @Bruce Johnson

Using the DESCENDANTS function may work.

=IF(Level@row = 3, IFERROR(AVG(DESCENDANTS([%Allocation]@row)), ""))

This gives the average of all the descendants, so if you want to calculate the average of only the bottom task or the children=0 task, you may want to add a helper column, like the one on the far right in the image below, and average the descendants.

=IFERROR(AVG(DESCENDANTS([Children = 0 %Allocation]@row)), "")

https://app.smartsheet.com/b/publish?EQBCT=6f7c00d17ed34a869415c26bdb587421

Otherwise, if someone inputs a %Allocation value into a cell that is not children=0, as shown in the yellow cell in the image above, you get a different average.

Bruce Johnson

Thank You, @jmyzk_cloudsmart_jp - That worked perfectly!!!

jmyzk_cloudsmart_jp

Happy to help!😁