Average Children they are not 0

jay.rathbun

Hello -

We are building a tracker that rolls up averages for a series of values across child rows. We have a top level value, with 12 children for each month, and each month as children rows of workers. So for example

Manager

Indent(January)

Indent(Indent(Worker 1)

We are trying to roll up for the manager, by month, as well as the month by the worker. We are seeing it work correctly when all the data is filled out for every worker in every month. However, if we only have data to March, the roll up to the manager for Jan-Mar is giving errors on averages. We have tried to remove the blank or 0 lines a few different ways, but the one that feels like it should be working and is not is

=AVERAGEIF(CHILDREN(), or =AVERAGEIF(CHILDREN(), <>0)

Any one have any advice on how to do this? If it helps, here is an example

The roll up works for the month, but not the level above that. The roll up even works for the month if some of children under it are blank but not others. I am surprised the parent wouldn't work the same way, since we have Jan/Feb/Mar data

Thanks for any help!

Paul Newcome

The problem with the top parent formula not working is that the error thrown in the Child row is being picked up. You would need to fix the error in the child row for the parent to work.

Give this a try...

=IFERROR(AVG(CHILDREN()), 0)

jay.rathbun

Thanks Paul, that did it!

Paul Newcome

Excellent. Happy to help. 👍️