Ranks for grouped data within a population

Gary,

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

Aladin

=======

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

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}

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.

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,

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 :

One my typo fix...

:

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.

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

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.

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.

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.

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.

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!!!

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!!!

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 :

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. ===

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

=========