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.
-
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
Parul Mishra
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!