Formula to Average a Children
Hello, I have a column of costs that i want to average in a parent row, however I only want to average values that are greater than $0.00 and have an active status (seperate column). Below is an example.
Status Cost
Parent Row Active X.XX
Child Row1 Active $4.00
Child Row2 Inactive $4.00
Child Row3 Active $2.00
Child Row4 Active $0.00
The formula would be in the Cost cell of the parent row and it should read $3.00 in this scenario since it would be averaging only Child Row1 (active and greater than $0) and Child Row3 (active and greater than $0)
I was trying to average them by summing the count of all children rows that are active and dividing that by child rows that are active and above 0, but i cant seem to straighten out the formula.
Any help appreciated.
Answers
-
=sumif(children(Status@row), "Active",children())/countif(Children(),not(@cell = 0))
Give that a try.
-
Thats close to what I had. The issue is that it when it executes the countif function it counts all rows that are not 0, but i need it to count all rows that are not 0 and are active.
-
=sumif(children(Status@row), "Active",children())/countifs(Children(),not(@cell = 0),children(status@row),"Active"
My bad. Give this one a try.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!