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

Anna Q

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

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