Rank Function Problem

JohnJay

New Member
Joined
Mar 7, 2002
Messages
37
Can anyone help with the following problem....I have Scores in Column C, and these scores are associated with data broken down into 3 subgroups in columns K, L, & M. Here is my problem...I just want to rank the scores in Column C that have the data in Columns K, L, & M in common. All this data is unsorted. Thank you all
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
On 2002-03-08 07:58, JohnJay wrote:
Can anyone help with the following problem....I have Scores in Column C, and these scores are associated with data broken down into 3 subgroups in columns K, L, & M. Here is my problem...I just want to rank the scores in Column C that have the data in Columns K, L, & M in common. All this data is unsorted. Thank you all
John, the only way I can figure out how to do this is using a two step approach. Somewhere you have to specify the criteria that you are looking for in columns K, L, & M so that you can rank the scores. For my purposes, I put them in Q2, R2, and S2. In column D I put in an array formula to compare columns K, L, & M to the values in Q1, R1, and S1. The formula was (an array formula meaning you have to press CTRL+SHIFT+ENTER instead of ENTER):

=IF(((K1:M1)=($Q$1:$S$1))*C1=0,"",((K1:M1)=($Q$1:$S$1))*C1)

This would put the value of C1 in D1 if all three columns (K, L, & M) equal the values in Q1, R1, & S1. Then in E1 I put this formula and copied down:

=IF(ISERROR(RANK(D1,$D$1:$D$8)),"",RANK(D1,$D$1:$D$8))

My example was using data in rows 1 to 8. You'll have to change to the appropriate number.

I don't know if this will help you out, but it's all I could think of.

Regards,
 
Upvote 0
looking at that I can see that it wont work for my purpose. I was hoping that there was a way for Excel to look at three columns, say K-M, simultaniously and then rank all scores, say in column C, that have K-M in common. Since K-M can contain any string, the rank fucntion would have to isolate those scores that have K-M in common and rank them as a group.

Thanks Barrie,

John
 
Upvote 0
On 2002-03-08 14:31, JohnJay wrote:
looking at that I can see that it wont work for my purpose. I was hoping that there was a way for Excel to look at three columns, say K-M, simultaniously and then rank all scores, say in column C, that have K-M in common. Since K-M can contain any string, the rank fucntion would have to isolate those scores that have K-M in common and rank them as a group.

Thanks Barrie,

John

John,

Care to provide a small sample of your data along with expected results?

Aladin
 
Upvote 0
Ok Aladin, Here is a sample...I sorted Division, Age, & category for easy viewing, but those columns will be sorted after ranking the scores according to groups. I hope this helps. As you can see each rank is isolated within its Division, Age, and Category group. Thank you very much..john

Score Rank Division Age Category
95 1 1 20-25 A
50 2 1 20-25 A
72 1 1 20-25 B
60 2 1 20-25 B
35 3 1 20-25 B
89 1 1 20-25 C
78 1 2 26-30 C
56 2 2 26-30 C
23 3 2 26-30 C
87 1 2 26-30 D
55 2 2 26-30 D
54 3 2 26-30 D
97 1 3 20-25 E
94 2 3 20-25 E
90 3 3 20-25 E
76 1 3 20-25 F
74 2 3 20-25 F
88 1 3 20-25 G
45 1 4 26-30 G
30 2 4 26-30 G
20 3 4 26-30 G
66 1 4 26-30 H
55 2 4 26-30 H
44 3 4 26-30 H
 
Upvote 0
On 2002-03-08 14:54, JohnJay wrote:
Ok Aladin, Here is a sample...I sorted Division, Age, & category for easy viewing, but those columns will be sorted after ranking the scores according to groups. I hope this helps. As you can see each rank is isolated within its Division, Age, and Category group. Thank you very much..john

Score Rank Division Age Category
95 1 1 20-25 A
50 2 1 20-25 A
72 1 1 20-25 B
60 2 1 20-25 B
35 3 1 20-25 B
89 1 1 20-25 C
78 1 2 26-30 C
56 2 2 26-30 C
23 3 2 26-30 C
87 1 2 26-30 D
55 2 2 26-30 D
54 3 2 26-30 D
97 1 3 20-25 E
94 2 3 20-25 E
90 3 3 20-25 E
76 1 3 20-25 F
74 2 3 20-25 F
88 1 3 20-25 G
45 1 4 26-30 G
30 2 4 26-30 G
20 3 4 26-30 G
66 1 4 26-30 H
55 2 4 26-30 H
44 3 4 26-30 H

John,

I'll assume that A1:E25 houses the sample data you provided.

B is the column whre you want the ranking determined by category, divison, and age.

In B2 array-enter and copy down as far as needed:

=MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))),0)

Note 1. This formula is an expanded version of the "RankIf" formula I devised regarding a similar question by Richard Larocque at the old board. If you have many of rows data, you'll experience a slowdown of your spreadsheet. In that case, you could resort to an alternative approach where space is traded off against time. I also formulated such an alternative again by Larocque's request. You could search the more 2 or 3 more recent archives to see how that alternative looks.

Note 2. The formula expects that the data are sorted by category, division, and age, the first being the most important.

Note 3. You need, just to be complete, to hit control+shift+enter at the same, not just enter, to enter an array-formula.

Aladin
 
Upvote 0
=MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))),0)

I'd recommend a minor reformulation:

{=MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW($1:$25)),0)}

which replaces,

...ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))...

with,

...ROW($1:$25)...

because:

1. COUNTIFs are very sluggish
2. LARGE always places #NUM! errors at end of its resultant array; therefore, MATCH will never seem them.

Note: that the ROW function argument in this reformulation must always include the 1st row of the worksheet.
This message was edited by Mark W. on 2002-03-08 16:20
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top