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!
Best Answer
-
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))))
Answers
-
=IFERROR(AVG(CHILDREN()), "")
This should do it, but you cannot set it up as a column formula without additional information.
-
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.
-
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))))
-
Hey @S Richardson, Thank you for sharing your solution!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions -
@S Richardson - thank you for all of this! made it much easier than all of the other advice I was trying out!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …