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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.