Column Formula to Leave Child Row Values Alone and Average or Sum them in Parent Row

So far I have been able to get three formulas to work, which include other formulas, but now I am having an issue pulling an average or a sum when the child rows do not have any formulas embedded. The following work when I convert to a column formula:

For a Sum:

=IFERROR(IF(C@row > 0, SUM(CHILDREN()), IF(OR(ISBLANK([Channel Margin]@row), ISBLANK(MAP@row)), " ", ROUNDUP((1 - [Channel Margin]@row) * MAP@row, 2))), "")

For an Avg:

=IFERROR(IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(OR(ISBLANK(Net@row), ISBLANK([Estimated Cost]@row)), " ", ROUND(IF([Estimated Cost]@row = 0, " ", (Net@row - [Estimated Cost]@row) / Net@row), 3))), "")

=IFERROR(IF(C@row > 0, AVG(CHILDREN()), IF(ISBLANK([Actual Cost]@row), " ", (ROUND(Net@row - [Actual Cost]@row) / Net@row))), "")

The following do not work to sum the child rows data and convert into a column formula:

=IFERROR(IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), "")) Incorrect Argument

=IF(C@row > 0, SUM(CHILDREN())) Breaks when I turn into a column formula and turns everything into a zero

Find more posts tagged with

Formulas

Accepted answers

All comments

parulmishra

Hi @Anna Q

I think there is an issue with the brackets on the formula

=IFERROR(IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), ""), "")

Anna Q

Hi @parulmishra

Thank you for responding! My issue with this formula is when I convert it to a column formula, it removes any values in the entire column. The child rows are hard coded.

I used a similar formula =IF(C@row > 0, SUM(CHILDREN())) and when I convert to a column formula, it removes the hardcoded child row values and the parent rows become 0.

parulmishra

Hi @Anna Q

Apologies for delay in response… but in your case you would need 2 columns, one where you put your hardcoded children values and second one which has the column formula. something like this

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.