Skip navigation

Sort operation on table mucks up formulae

283 Views 5 Replies Latest reply: Apr 17, 2013 6:20 PM by lovesthatmac RSS
lovesthatmac Calculating status...
Currently Being Moderated
Apr 16, 2013 5:19 AM

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:

 

table.jpg

 

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 (12,820 points)
    Currently Being Moderated
    Apr 16, 2013 7:05 AM (in response to lovesthatmac)

    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:

    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:

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

  • Jerrold Green1 Level 7 Level 7 (28,290 points)
    Currently Being Moderated
    Apr 16, 2013 7:21 AM (in response to lovesthatmac)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Apr 17, 2013 12:04 AM (in response to lovesthatmac)

    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.

     

    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.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.