Sort operation on table mucks up formulae

Wayne Contello Austin, Texas
the basic solution is to use functions that are "sortsafe" generally this means using sumif(), sum(), vlookup(), match(), row(), column(), etc rather directly referencing cells.
Here a simpler example where I placed the summary information in a summary table:
The following three formulas attemp to sum the largest n values. This almost is perfect except in the case where there are repeats of the same value:
A3=SUMIF(Data::D,">"&LARGE(Data::D, B2))
A4=SUMIF(Data::D,">"&LARGE(Data::D, B3))
A5=SUMIF(Data::D,">"&LARGE(Data::D, B4))
This is the sum of all the values in column D:
A6=SUM(Data::D)
This is the count of how many entries there are in column D:
B2=COUNTA(Data::D)
These are the percentage stats:
C3=100*A3/$A$6
C4=100*A4/$A$6
C5=100*A5/$A$6
This will survive a sort:

Like (0)


LTM,
Numbers, and other spreadsheet programs, are smart enough to follow your cells around even if you shuffle them, sort them, manually move them, etc. That's something you don't want now, but it's the general case.
A workable type of cell reference for your situation is the OFFSET function, in conjunction with INDIRECT. Neither of these pays any attention to sorts or moves.
Here's an example of how you could always grab the sum of E3 to E22:
Leaving out the equal sign and other parts of your expression...
SUM(OFFSET(INDIRECT("E3"), 0,0, 20))
What that says is "sum the range beginning at E3, wiith no row offset and no column offset, the range extending for 20 rows".
Regards,
Jerry

Like (0)


Thanks so much for the pointers  I'll try those things and see how I go!
Tell me, while I'm reworking things, is it possible to have the sort occur automatically, or does it still have to be a manual Sort Descending operation?
Also, is it possible to freeze the first column out of the sort? Perhaps just by making it a header column or something maybe?

Like (0)


Hi LTM,
Here's an alternate approach:
Separate your data entry and report into two tables.
Data is used only for entering the data for each retailer, and the retailer list stays in the same order.
Report automatically updates the sorted list into rank order, using the rank values calculated in column A of the Data table:
Formula, Data:
A2, and filled down: =RANK(F,F,)+ROW()/100000
RANK calculates a whole number ranking. the part after the + sign adds a small fractional value dependent on the row to allow for situations where two retailers (eg. store 8 and Store 9) have identical sales averages.
Formulas, Report:
A3, and filled right and down:
A3: =VLOOKUP(SMALL(Data :: $A,ROW()2),Data :: $A:$F,COLUMN(),FALSE)
A1: ="Of "&COUNTA(B)&", retailers, the top 10 account for "&ROUND(100*SUM(F3:F12)/SUM(F),0)&" % of sales, the top 5 account for "&ROUND(100*SUM(F3:F7)/SUM(F),0)&" % of sales, and the top 2 account for "&ROUND(100*SUM(F3:F4)/SUM(F),0)&" % of sales."
Revise the numbers and ranges to match your case of 20, 10 and 5 items to be summed where I have 10, 5 and 2.
Regards,
Barry
PS: The Rank column on the data table may be simply distracting. It has to be there, and it has to be column A, but it can be hidden without affecting operation of the formulas.
B.

Like (0)


Thanks so much Barry, Jerry and Wayne for those approaches. I'll go through them in detail (during kid free hours) and make this thing work. Will be interesting to see which approach suits best in the end. I really appreciate the help!

Like (0)
