How to attain an average to the lowest level hierarchy

Bud Law

Hello,

I'm trying to attain a formula that would provide an average of the lowest level of children in a hierarchy. Currently I'm using a =AVG(CHILDREN()) formula, but based on the amount of data we are collecting, the highest most level of hierarchy is beginning to vary significantly based on the fact it is only capturing the average of it's children at the first level vs. what the average should be at the lowest level. I've attempted to manipulate it using AVERAGEIF formulas and IF formulas, but can't seem to get this one worked out.

Paul Newcome

I would suggest inserting a text/number column with the following column formula:

=COUNT(ANCESTORS())

Then you can use something along the lines of

=AVERAGEIF([New Column]:[New Column], MAX([New Column]:[New Column]), [Column To Average]:[Column To Average])

Bud Law

This works for the highest level hierarchy, but when copying it to the second level or lower it creates a circular reference...which makes sense because I'm then literally asking for the exact same information and it would only pull the same results. I think I can work with this though as the scenario I'm working through calls for an average by year, then month, week, and day. The highest variance I would expect to encounter would be when reviewed by the year.

Thank you for the advice. I already had the ancestor count formula in place for conditional formatting and had attempted to create a formula similar to what you shared above, but just couldn't get it straightened out.

Paul Newcome

Try putting it in a different column than the one that contains the values you are wanting to average.

Another option that MIGHT work:

=AVERAGEIF(DESCENDANTS([New Column]@row), @cell = MAX(DESCENDANTS([New Column]@row), DESCENDANTS())