5 Replies Latest reply: Apr 17, 2013 6:20 PM by lovesthatmac
lovesthatmac Level 1 Level 1

Hi there,


I'm hoping there are some smarter people than me here…


There usually are…


I have a table which is meant to list some sales results from different retail shops in descending sales order, and I'm having trouble with a couple of aspects of it.


To put things into context, the table looks like this:




And the following points about the table are relevant:


- The last four columns reference sales data from other sheets in the document.


- The table is ordered by manually selecting "Sort Descending" on the last column.


- The "first" column is actually a separate table up against the main table (so that the descending numbers in that "first" column aren't affected by the "Sort Descending" operation done on the last column).


- The formulae in the top row obtains its information from the last column, and the formulae looks like this:


top formulae 1.jpg



But the main problem that happens is the following:


Just say that the sales from Store 4 drops from 5.01 units per week down to 1.52 units per week. The table then looks like:


table 2.jpg



Then, when I do the Sort Descending operation on the last column, it looks like this (Store 4 drops down to its new place in the sales order):


table 3.jpg



However, doing that Sort Descending operation on the last column somehow mucks up the formulae in the top row, which now looks like this:


formulae 2.jpg


And every time the last column has changed values, then a Sort Descending operation done on it, that top row formulae gets more and more muddled up. (I've tried using the Absolute $ in front of the E column references in that formulae, but it doesn't help.)


So my questions are:


1. How do I get that top row formulae to work?


2. Is there a way to have the last column sort itself automatically rather than having to do a manual Sort Descending on it?


3. Is there a way to have a first column of the table that is unaffected by the sort operation on the last column (but the middle columns still change with the last column)?



Any help would be greatly appreciated!

MacBook Pro, Mac OS X (10.7.2)
  • Wayne Contello Level 6 Level 6

    the basic solution is to use functions that are "sort-safe"-- 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:

    Screen Shot 2013-04-16 at 9.02.03 AM.png

    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:



    This is the count of how many entries there are in column D:




    These are the percentage stats:






    This will survive a sort:

    Screen Shot 2013-04-16 at 9.04.25 AM.png

  • Jerrold Green1 Level 7 Level 7



    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".





  • lovesthatmac Level 1 Level 1

    Thanks so much for the pointers - I'll try those things and see how I go!


    Tell me, while I'm re-working 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?

  • Barry Level 7 Level 7

    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:

    Picture 2.png

    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.





    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.


  • lovesthatmac Level 1 Level 1

    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!