Top and Bottom 5 Values in a Range

[polecat89]

I have seen various topics that have dealt with this issue at least in part, but I am still having difficulty achieving the desired result. I am starting with a range A4:B18 that contains the names of 15 stores and their monthly sales and am attempting to return the top and bottom 5, displaying store name and # of sales in adjacent cells. I want to include duplicates. I have 15 in E1 and in E2 the formula below and copied down.

=IF(ROWS(E$4:E4)<=$E$2,LARGE($B$4:$B$18,ROWS(E$4:E4)),"")

In F4 I have the formula below and copied down:

{=IF(N($E$4),INDEX(A$4:A$18,SMALL(IF(B$4:B$18=E4,ROW(B$4:B$18)-ROW(B$4)+1),COUNTIF(E$4:E4,E4))),"")}

This works great for the top 5, but I run into a problem with the Bottom 5. The formula below returns the bottom 5 sales numbers in reverse order with no problem:

=IF(ROWS(H$4:H4)<=$E$2,SMALL($B$4:$B$18,ROWS(H$4:H4)),"")

However, I cannot seem to get the corresponding bottom 5 stores in reverse order. What would I need to change below? As is it returns the top 5 as above.

{=IF(N($E$4),INDEX(A$4:A$18,SMALL(IF(B$4:B$18=E4,ROW(B$4:B$18)-ROW(B$4)+1),COUNTIF(E$4:E4,E4))),"")}

 

[polecat89]

Alternatively, I tried something below, which does work, however does not include duplicates, and requires that second formula to first rank the sales in order for the third formula to order the store names.

=IF(ROW()-ROW($K$4:$K$18)+1>$K$1,"",LARGE($B$4:$B$18,ROW()-ROW($K$4:$K$18)+1))

=RANK(B4,B$4:B$18)+COUNTIF(B$4:B4,B4)-1

=OFFSET(A$4,MATCH(SMALL(L$4:L$18,ROW()-ROW(M$4)+1),L$4:L$18,0)-1,0)

Repeating this for the bottom except change the second formula to:

=COUNT(B$4:B$18)-(RANK(B4,B$4:B$18)+COUNTIF(B$4:B4,B4))+2

 

[scottylad2]

IF(ROWS(H$4:H4)<=$E$2,SMALL($B$4:$B$18,ROWS(H4:H$8)),"")

try this, the Red will start you at 5 and as you drag down count down to 1
</pre>

 

[markmdz]

Re: Top and Bottom 5 (Percentage) Values in a Range

Didn't want to start a new thread for this because it's very similar to this thread topic....

So I have a massive list of accounts listed in a row (B4:LD4), which I track the sales of on a daily basis, then sum on a monthly.

Right now I'm tracking percentage of change from one month to the next in row 6 (B6:LD6) (ie. FEB is tracked B8:LD8 and MAR is B9:LD9).

On another sheet within the workbook, I want to track the top and bottom 10 accounts based on that percentage of change (basically pull the value from B6:LD6), but also want to capture the Account Name (B4-LD4) and the FEB (B8:LD8) and MAR (B9:LD9) values.

Pulling the top and bottom 10 values is not that big of a deal, but I don't know how to pull the other three rows based on the top and bottom values. (So if one of the top 10 values is found in cell AB6, I want to capture that value, then also capture AB4, AB8, and AB9.

Bare in mind, many of the accounts listed have a Div/O error because they did $0 in sales both FEB and MAR. I do not want to return these Accounts on the Top & Bottom 10 results.

That sheet would then look like:

Top 10:
(spanned 10 columns out)
Row 4: Account Name (Source Data Range: 'Sales_2011'!B4-LD4)
Row 5: Percentage of Change (Source Data Range: 'Sales_2011'!B6:LD6)
Row 6: FEB (Source Data Range: 'Sales_2011'!B8:LD8)
Row 7: MAR (Source Data Range: 'Sales_2011'!B9:LD9)

Bottom 10:
(spanned 10 columns out)
Row 4: Account Name (Source Data Range: 'Sales_2011'!B4-LD4)
Row 5: Percentage of Change (Source Data Range: 'Sales_2011'!B6:LD6)
Row 6: FEB (Source Data Range: 'Sales_2011'!B8:LD8)
Row 7: MAR (Source Data Range: 'Sales_2011'!B9:LD9)

I just can't seem to get my head around this one at the moment, so any help is greatly appreciated. Am I trying to do too much at once? I'm just a bit confuddled on this one as I'm by no means great at excel. The next doozy I'm going to start a thread about is some dynamic charting based on account name. That's one I've been tinkering with for a while now. ..I'm so happy I found this forum. Hopefully someone will take mercy on my and put me out of my misery by tossing out some help.