Working with dynamic cumulative fractions
I am trying to create pareto charts for dashboards. I am referencing a sheet where I create totals and counts etc. One is a count of total items in a team. Impact =(Count@row / (SUM(CHILDREN(Count$2))) to get percentage for items for each team overall. Cumulative is for Pareto charts =SUMIFS(Impact$3:Impact$20, Row$3:Row$20, @cell <= Row@row). Row is a helper column that holds the row number. The problem is, when I sort the column Impact, The formula in Cumulative changes. So the absolute row numbers will not stay the same. For instance if I sort Impact in Descending the formula in Cumulative becomes =SUMIFS(Impact$17:Impact$3, Row$17:Row$3, @cell <= Row@row). Why are the absolute values changing? I update this sheet manually when the count changes and sort Impact column, but if the formula in Cumulative changes, the Pareto is all messed up.
Best Answer
-
The absolute values are changing because on the back-end they are not stored as referencing row 3 (for example) but for row ID 12345 that happens to be on row 3 when you create the absolute reference. When you sort, row ID 12345 moves down to row 17, so your absolute reference changes to that.
The way to avoid this is to write a formula that does not require absolute references. You can do this by including an argument where the Row column is greater than or equal to 3 and less than or equal to 20, or (my personal preference) would be to just reference child rows with a CHILDREN function the same way you did in your Impact column.
=SUMIFS(CHILDREN(Impact$2), CHILDREN(Row$2), @cell <= Row@row)
Answers
-
The absolute values are changing because on the back-end they are not stored as referencing row 3 (for example) but for row ID 12345 that happens to be on row 3 when you create the absolute reference. When you sort, row ID 12345 moves down to row 17, so your absolute reference changes to that.
The way to avoid this is to write a formula that does not require absolute references. You can do this by including an argument where the Row column is greater than or equal to 3 and less than or equal to 20, or (my personal preference) would be to just reference child rows with a CHILDREN function the same way you did in your Impact column.
=SUMIFS(CHILDREN(Impact$2), CHILDREN(Row$2), @cell <= Row@row)
-
Perfect, Thank you. I will try it.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!