Rank Function Problem

[JohnJay]

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

[Barrie Davidson]

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,

[JohnJay]

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

[Aladin Akyurek]

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

[JohnJay]

Barrie, I also found this that might be useful to you.

http://www.rb-ad.dircon.co.uk/rob/ex...lumnletter.htm

Using that helped solve my column problems

[JohnJay]

Aladin,

I can. Let me find an sheet that had a great example. brb

[Barrie Davidson]

John, you've got the right man on the job now. I'm curious to see what Aladin comes up with.

[JohnJay]

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

[Aladin Akyurek]

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

[Mark W.]

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