Sum child rows that use a formula?

I have child rows that use a formula to calculate % completion based on input to another column. I'd like the parent row to return a value based on the child values (Specifically, sum of the child rows divided by the number of child rows).

As the child rows use a formula rather than a direct number entry, entering a =SUM(CHILDREN()) / COUNT(CHILDREN()) formula in the parent row doesn't work.

I'm not sure how to manage the data to get the result I'm seeking. Thanks for any thoughts you have!

Find more posts tagged with

Formulas

Accepted answers

S Richardson

Solved: I needed to change the value output of the formula in the child rows, and now the parent row AVG formula works as expected.

=IF(Progress@row = "Complete", 1, IF(Progress@row = "In Progress", .25, IF(Progress@row = "Not Started", 0, IF(Progress@row = "On Hold", .25))))

All comments

Carson Penticuff

=IFERROR(AVG(CHILDREN()), "")

This should do it, but you cannot set it up as a column formula without additional information.

S Richardson

Using that formula results in just a blank, unfortunately. It won't be a column formula, as there's other formulas in different rows within the same column.

I tried the formula =AVG(CHILDREN()) as well, which resulted in an error #DIVIDE BY ZERO

To provide a bit more detail:

I have five rows that use the formula =IF(Progress@row = "Complete", "100%", IF(Progress@row = "In Progress", "25%", IF(Progress@row = "Not Started", "0%", IF(Progress@row = "On Hold", "25%"))))

I want the parent row to average the returned values from those five child rows.

S Richardson

Solved: I needed to change the value output of the formula in the child rows, and now the parent row AVG formula works as expected.

=IF(Progress@row = "Complete", 1, IF(Progress@row = "In Progress", .25, IF(Progress@row = "Not Started", 0, IF(Progress@row = "On Hold", .25))))

Marcela

Hey @S Richardson, Thank you for sharing your solution!

ssummer

@S Richardson - thank you for all of this! made it much easier than all of the other advice I was trying out!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.