MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ranks for grouped data within a population


Posted by Gary Fogelbach on November 10, 2001 1:59 PM

I am having some difficulty determining the Rank for groups of data within a database. I have a 3 column database. First column is job, second is Avg Salary, third is respondent. There are 5 Avg Salaries reported for each job by 5 different respondents to a survey. There are 6 different jobs. This is a simplified version of a huge database. I need to determine the rank of each respondent for each job. For example, for Job #1, the average salary of Respondent A may Rank First, for Job #2 Respondent A may rank 5th, etc. I tried using a CSE formula with RANK combined with an IF function, but Excel wouldn't accept the formula. I would be very grateful for any suggestions. Thanks so much.


Posted by Aladin Akyurek on November 10, 2001 2:19 PM

Gary,

Care to post 10 rows of your data along with the expected results?

Aladin

=======

Posted by Gary F on November 10, 2001 2:45 PM

Here are 10 Rows, Aladin. Rank is the expected result. Thanks for your help.

Job# Avg Resp Rank
1101 43812 A 4
1101 49656 B 1
1101 40737 C 5
1101 46555 D 2
1101 45312 E 3
1102 20646 A 5
1102 22172 B 4
1102 24123 C 2
1102 23188 D 3
1102 27615 E 1

Posted by Mark W. on November 10, 2001 2:47 PM

You really didn't specify the basis for the ranking.
Would the lowest salary in each job qualify be
ranked 1st? I had to assume something so that
was my assumption. Using the following data
set (based on your description) in cells A1:C31...

{"Job","Avg Salary","Responent"
;1,71835,"A"
;1,82682,"B"
;1,76912,"C"
;1,58490,"D"
;1,93316,"E"
;2,67710,"A"
;2,94963,"B"
;2,83441,"C"
;2,80485,"D"
;2,57172,"E"
;3,52355,"A"
;3,96509,"B"
;3,67471,"C"
;3,93955,"D"
;3,89184,"E"
;4,80027,"A"
;4,56587,"B"
;4,52107,"C"
;4,55348,"D"
;4,84386,"E"
;5,68113,"A"
;5,96537,"B"
;5,97722,"C"
;5,86961,"D"
;5,55017,"E"
;6,89544,"A"
;6,70401,"B"
;6,69269,"C"
;6,52326,"D"
;6,61038,"E"}

I used the array formula...

{=SUM((IF(A2=Job,'Avg Salary')<B2)+0)+1}

...to get...

{"Job","Avg Salary","Responent","Rank"
;1,71835,"A",2;1,82682,"B",4
;1,76912,"C",3;1,58490,"D",1
;1,93316,"E",5;2,67710,"A",2
;2,94963,"B",5;2,83441,"C",4
;2,80485,"D",3;2,57172,"E",1
;3,52355,"A",1;3,96509,"B",5
;3,67471,"C",2;3,93955,"D",4
;3,89184,"E",3;4,80027,"A",4
;4,56587,"B",3;4,52107,"C",1
;4,55348,"D",2;4,84386,"E",5
;5,68113,"A",2;5,96537,"B",4
;5,97722,"C",5;5,86961,"D",3
;5,55017,"E",1;6,89544,"A",5
;6,70401,"B",4;6,69269,"C",3
;6,52326,"D",1;6,61038,"E",2}

Posted by Mark W. on November 10, 2001 2:49 PM

Repost to Fix some Typo's

: I am having some difficulty determining the Rank for groups of data within a database. I have a 3 column database. First column is job, second is Avg Salary, third is respondent. There are 5 Avg Salaries reported for each job by 5 different respondents to a survey. There are 6 different jobs. This is a simplified version of a huge database. I need to determine the rank of each respondent for each job. For example, for Job #1, the average salary of Respondent A may Rank First, for Job #2 Respondent A may rank 5th, etc. I tried using a CSE formula with RANK combined with an IF function, but Excel wouldn't accept the formula. I would be very grateful for any suggestions. Thanks so much.

Posted by Mark W. on November 10, 2001 2:53 PM

Okay, look like 1st goes to the highest... Use this...

{=SUM((IF(A2='Job#','Avg')+0 > B2)+0)+1}

...where your data resides in columns A:C. 1101 43812 A 4 1101 49656 B 1 1101 40737 C 5 1101 46555 D 2 1101 45312 E 3 1102 20646 A 5 1102 22172 B 4 1102 24123 C 2 1102 23188 D 3 1102 27615 E 1

: Gary,

Posted by Mark W. on November 10, 2001 2:55 PM

I should mention...

...that 'Job#' and 'Avg' are named ranges.
You can use the cell ranges themselves if
you wish. : 1101 43812 A 4 : 1101 49656 B 1 : 1101 40737 C 5 : 1101 46555 D 2 : 1101 45312 E 3 : 1102 20646 A 5 : 1102 22172 B 4 : 1102 24123 C 2 : 1102 23188 D 3 : 1102 27615 E 1 :

Posted by Mark W. on November 10, 2001 2:58 PM

One my typo fix...

:

Posted by Gary F. on November 10, 2001 3:14 PM

Re: I should mention...

Thanks for the quick response, Mark! I'll give it a shot. ...that 'Job#' and 'Avg' are named ranges. You can use the cell ranges themselves if you wish. : {=SUM((IF(A2='Job#','Avg')+0 > B2)+0)+1} : ...where your data resides in columns A:C.

Posted by Aladin Akyurek on November 10, 2001 3:37 PM

Gary --

Here is a different approach. I'll assume your sample data to be in A1:C11 including labels.

In D2 enter: =SUMPRODUCT(MATCH(B2,LARGE(INDIRECT(ADDRESS(MATCH(A2,$A:$A,0),2)&":"&ADDRESS(MATCH(A2,$A:$A,0)+4,2)),{1,2,3,4,5}),0))

Copy this down as far as needed.

If any questions, let me know.

Aladin

========== Job# Avg Resp Rank 1101 43812 A 4 1101 49656 B 1 1101 40737 C 5 1101 46555 D 2 1101 45312 E 3 1102 20646 A 5 1102 22172 B 4 1102 24123 C 2 1102 23188 D 3 1102 27615 E 1

Posted by Gary F on November 10, 2001 3:39 PM

Re: I should mention...

Hey Mark: Would you mind explaining a bit about how that formula works, please? I can't seem to get it to return the rank for each job. Thanks. ...that 'Job#' and 'Avg' are named ranges. You can use the cell ranges themselves if you wish. : {=SUM((IF(A2='Job#','Avg')+0 > B2)+0)+1} : ...where your data resides in columns A:C.


Posted by Gary on November 10, 2001 3:51 PM

Using your data set in cells A1:C11...

1. Enter the array formula...
{=SUM((IF(A2=$A$2:$A$11,$B$2:$B$11)+0 > B2)+0)+1}
into cell D2 as an array formula.

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

2. Copy the formula in cell D2 down to cell D11

This should produce the results you want!!!

Hey Mark: Would you mind explaining a bit about how that formula works, please? I can't seem to get it to return the rank for each job. Thanks. : ...that 'Job#' and 'Avg' are named ranges. : You can use the cell ranges themselves if : you wish.


Posted by Mark W. on November 10, 2001 3:51 PM

Re: I should mention...

Using your data set in cells A1:C11...

1. Enter the array formula...
{=SUM((IF(A2=$A$2:$A$11,$B$2:$B$11)+0 > B2)+0)+1}
into cell D2 as an array formula.

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

2. Copy the formula in cell D2 down to cell D11

This should produce the results you want!!!

Hey Mark: Would you mind explaining a bit about how that formula works, please? I can't seem to get it to return the rank for each job. Thanks. : ...that 'Job#' and 'Avg' are named ranges. : You can use the cell ranges themselves if : you wish.


Posted by Mark W. on November 10, 2001 3:54 PM

Re: I should mention...

Using your data set in cells A1:C11...

1. Enter the array formula...
{=SUM((IF(A2=$A$2:$A$11,$B$2:$B$11)+0 > B2)+0)+1}
into cell D2 as an array formula.

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

2. Copy the formula in cell D2 down to cell D11

This should produce the results you want!!!

Hey Mark: Would you mind explaining a bit about how that formula works, please? I can't seem to get it to return the rank for each job. Thanks. : ...that 'Job#' and 'Avg' are named ranges. : You can use the cell ranges themselves if : you wish.


Posted by Mark W. on November 10, 2001 4:02 PM

Aladin, this is my posting -- not Gary's! Sorry for the confusion. (nt)

1. Enter the array formula... {=SUM((IF(A2=$A$2:$A$11,$B$2:$B$11)+0 > B2)+0)+1} into cell D2 as an array formula. Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, {}, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. 2. Copy the formula in cell D2 down to cell D11 This should produce the results you want!!!


Posted by Mark W. on November 10, 2001 4:02 PM

Aladin, this is my posting -- not Gary's! Sorry for the confusion. (nt)

1. Enter the array formula... {=SUM((IF(A2=$A$2:$A$11,$B$2:$B$11)+0 > B2)+0)+1} into cell D2 as an array formula. Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, {}, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. 2. Copy the formula in cell D2 down to cell D11 This should produce the results you want!!!


Posted by Mark W. on November 10, 2001 4:23 PM

This approach fails if the list isn't sorted by
by Job# or if there are not 5 respondents for
each Job#. Gary -- Here is a different approach. I'll assume your sample data to be in A1:C11 including labels. In D2 enter: =SUMPRODUCT(MATCH(B2,LARGE(INDIRECT(ADDRESS(MATCH(A2,$A:$A,0),2)&":"&ADDRESS(MATCH(A2,$A:$A,0)+4,2)),{1,2,3,4,5}),0)) Copy this down as far as needed. If any questions, let me know. === : Job# Avg Resp Rank : 1101 43812 A 4 : 1101 49656 B 1 : 1101 40737 C 5 : 1101 46555 D 2 : 1101 45312 E 3 : 1102 20646 A 5 : 1102 22172 B 4 : 1102 24123 C 2 : 1102 23188 D 3 : 1102 27615 E 1 :

Posted by Aladin Akyurek on November 10, 2001 4:27 PM

That's right. Omitted the caveat, because I expect the data to be sorted (as survey data usually are) by Job#. If not, it should be sorted by Job#.

========= This approach fails if the list isn't sorted by by Job# or if there are not 5 respondents for each Job#. : Gary -- : Here is a different approach. I'll assume your sample data to be in A1:C11 including labels. : In D2 enter: =SUMPRODUCT(MATCH(B2,LARGE(INDIRECT(ADDRESS(MATCH(A2,$A:$A,0),2)&":"&ADDRESS(MATCH(A2,$A:$A,0)+4,2)),{1,2,3,4,5}),0)) : Copy this down as far as needed. : If any questions, let me know. ===

Posted by Aladin Akyurek on November 11, 2001 7:50 AM

Gary & Mark: Another proposal with RANK

I'll assume Gary's sample data in A1:C11 including labels (Job#, etc), that is,

{"Job#","Avg","Resp";1101,43812,"A";1101,49656,"B";1101,40737,"C";1101,46555,"D";1101,45312,"E";1102,20646,"A";1102,22172,"B";1102,24123,"C";1102,23188,"D";1102,27615,"E"}

Make a list of unique Job# in column D from D2 on. This can be done e.g., by means of Advanced Filter.

Make a list of Respondents in the top row, that is, from G1 to K1.

In E2 enter: =ADDRESS(MATCH(D2,$A:$A,0),2)&":"&ADDRESS(MATCH(D2,$A:$A,0)+4,2)

Copy down this as far as needed.

In F2 enter: =ADDRESS(MATCH(D2,$A:$A,0),3)&":"&ADDRESS(MATCH(D2,$A:$A,0)+4,3)

Copy down this as far as needed.

These 2 formulas require that the data have been sorted on Job#.

Finally:

In G2 enter: =RANK(INDEX(INDIRECT($E2),MATCH(G$1,INDIRECT($F2),0)),INDIRECT($E2))

Copy this across then down.

The result section (D1:K3) will show the following:

{"","","","A","B","C","D","E";
1101,"$B$2:$B$6","$C$2:$C$6",4,1,5,2,3;
1102,"$B$7:$B$11","$C$7:$C$11",5,4,2,3,1}

In fact a summary table that pulls everything together.

The array formula (by Mark) is better than the SUMPRODUCT formula (by me) on two counts: shorter and I believe more efficient. (Although the array formula doesn't require sorting, results based on unsorted data would be a bit harder to process.)

The above system, however, should be a bit more efficient in terms of very large sets of data and the resulting "summary" table is more accessible to Excel and to us.

Aladin

=========