Formula to Average a Children

COREY GILL

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.

L_123

=sumif(children(Status@row), "Active",children())/countif(Children(),not(@cell = 0))

Give that a try.

COREY GILL

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.

L_123

=sumif(children(Status@row), "Active",children())/countifs(Children(),not(@cell = 0),children(status@row),"Active"

My bad. Give this one a try.