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
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!