Average Children they are not 0
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!
Answers
-
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)
-
Thanks Paul, that did it!
-
Excellent. Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!