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
Answers

Hi @Anna Q
I think there is an issue with the brackets on the formula
=IFERROR(IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), ""), "")
Parul Mishra

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.
Help Article Resources
Categories
Check out the Formula Handbook template!