Average Children they are not 0
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
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!
Help Article Resources
Check out the Formula Handbook template!