Top 5 in group and bottom 5 in group

[Guest]

I have an spreadsheet that has different sales reps in column A.

In column B is the District the sales rep resides in. There could be many
sales reps per District.

In column C are the number of sales the sales rep had.

Is there a way to get PER DISTRICT the top 5 employees according to their
number of sales?

For example, in
District1 John Doe $2M
District1 Jack Doe $1.5M
District1 Suzy Doe $1M
etc.

District2 Michael Doe $5M
District2 Michelle Doe $4.5M
etc.


Thanks so much.

 

[Bob Phillips]

This gets the name of the top person

=INDEX(B1:B100,MATCH(LARGE(IF(A1:A100="District1",C1:C100),ROW(A1)),C1:C100,
0))

as an array formula, so commit with Ctrl-SHift-Enter.

Drag down 4 rows to get 2,3,4,5. To get the amount, replace the first
B1:B100 by C1:C100

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi

One way would be to use a Pivot Table.
Mark your range of source data. Data>Pivot Table>Next>Next> and on Layout
drag the District Field to the Row area, drag the Sales Rep filed to the Row
area to the right of District, drag the Sales filed to the data area > Finish

Now, double click the Sales Rep Filed>Advanced>click the radio button for
Show Top and Select 5 as the number.

For more help on Pivot tables take a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and
http://www.contextures.com/xlPivot05.html


Regards

Roger Govier