Working with dynamic cumulative fractions

GregoryAA

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.

Paul Newcome

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)