Identifying the top five values in multiple groups
 

I've got a spreadsheet of pay information for about eight hundred people.
Each person is on one of eight salary scales

I'd like to create a new worksheet that shows the details of just the 5
highest paid people in each scale (name, dept, salary, etc.) - and also the
five lowest. Ideally, I'd like also to be able to vary that number - eg the
top ten, the highest, etc..

Can someone help?

Thanks

Identifying the top five values in multiple groups
 

Suppose you have data in Sheet1 in the below format

Col A Col B Col C
Name Scale Salary
a1 1 101
a2 1 102
a3 1 103
a4 2 104


In Sheet2 cell A1 enter the scale
In Sheet2 cell B1 enter the number of items to be displayed

In cell A2 apply the below array formula and copy down to 10 cells
=IF($B$1=ROW(A1),LARGE(IF(Sheet1!$B$1:$B$100=$A$1 ,
Sheet1!$C$1:$C$100),ROW(A1)),"")

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"


In cell B2 apply the below formula and copy/drag to 10 cells
=IF(A2<"",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0) ),"")

For other columns change the column in the second formula (INDEX() ) to suit.

--
Jacob (MVP - Excel)


"RobFJ" wrote:

I've got a spreadsheet of pay information for about eight hundred people.
Each person is on one of eight salary scales

I'd like to create a new worksheet that shows the details of just the 5
highest paid people in each scale (name, dept, salary, etc.) - and also the
five lowest. Ideally, I'd like also to be able to vary that number - eg the
top ten, the highest, etc..

Can someone help?

Thanks

Identifying the top five values in multiple groups
 

Thanks Jacob,

I've tried the suggested formulae and they work as long as everything is
unique values. If there are two values the same, then the index formula
seems to repeat the first value it comes across.

Is there a way around that?

Regards,





"Jacob Skaria" wrote in message
...
Suppose you have data in Sheet1 in the below format

Col A Col B Col C
Name Scale Salary
a1 1 101
a2 1 102
a3 1 103
a4 2 104


In Sheet2 cell A1 enter the scale
In Sheet2 cell B1 enter the number of items to be displayed

In cell A2 apply the below array formula and copy down to 10 cells
=IF($B$1=ROW(A1),LARGE(IF(Sheet1!$B$1:$B$100=$A$1 ,
Sheet1!$C$1:$C$100),ROW(A1)),"")

Please note that this is an array formula. You create array formulas in
the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER
to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"


In cell B2 apply the below formula and copy/drag to 10 cells
=IF(A2<"",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0) ),"")

For other columns change the column in the second formula (INDEX() ) to
suit.

--
Jacob (MVP - Excel)


"RobFJ" wrote:

I've got a spreadsheet of pay information for about eight hundred people.
Each person is on one of eight salary scales

I'd like to create a new worksheet that shows the details of just the 5
highest paid people in each scale (name, dept, salary, etc.) - and also
the
five lowest. Ideally, I'd like also to be able to vary that number - eg
the
top ten, the highest, etc..

Can someone help?

Thanks

Identifying the top five values in multiple groups
 

Excel 2007 PivotTable
Top, bottom 5
All ties will show.
No code, no formulas.
Animated.
http://c0718892.cdn.cloudfiles.racks.../04_27_10.xlsx
Pdf preview:
http://www.mediafire.com/file/w3jmmgm0jmj/04_27_10.pdf

Identifying the top five values in multiple groups
 

RobFJ wrote:
I've got a spreadsheet of pay information for about eight hundred
people. Each person is on one of eight salary scales

I'd like to create a new worksheet that shows the details of just the 5
highest paid people in each scale (name, dept, salary, etc.) - and also
the five lowest. Ideally, I'd like also to be able to vary that number -
eg the top ten, the highest, etc..

Can someone help?

Thanks







Try a PivotTable. If you aren't familiar with them, look he

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Hi,

You can use either the SMALL or LARGE function to solve this problem
quickly. =SMALL(D2;D11,1) returns the smallest number in the range,
=SMALL(D2;D11,2) returns the second-smallest number, and so on. Unlike
the RANK function, the SMALL and LARGE functions deal well with ranges that
contain ties.


Similary =LARGE(D2;D11,1) brings you the first largest number.

try this

Thanks
Bala

Identifying the top five values in multiple groups
 

Herbert,

Thanks for the response.

However, I couldn't get anything to work in the following download - which
took me to a zipped file :

http://c0718892.cdn.cloudfiles.racks.../04_27_10.xlsx

The best I can get is if I click onto something, IE opens and code is shown

Is there something missing or something I should be doing (other than
extracting them and looking for something relevant to open or run.)

Thanks

Rob

Identifying the top five values in multiple groups
 

Excel 2007 PivotTable
Cloud file checks out OK,
but here is an alternate site:
http://www.mediafire.com/file/n1uhegtmlzw/04_27_10.xlsx