Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Top 5 in group and bottom 5 in group
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. |
#2
|
|||
|
|||
Top 5 in group and bottom 5 in group
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) "jhicsupt" wrote in message ... 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. |
#3
|
|||
|
|||
Top 5 in group and bottom 5 in group
Hi
One way would be to use a Pivot Table. Mark your range of source data. DataPivot TableNextNext 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 FiledAdvancedclick 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 jhicsupt wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
Average a group of tests for grade, some tests not taken by all. | Excel Discussion (Misc queries) | |||
How do I see the sum or other function in the bottom bar of my Ex. | Excel Worksheet Functions |