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

 

[JohnJay]

I think maybe my last post misled those of you who are trying to help. I entered both versions of the formulas and got all 1's in the rank column. I sorted the data I posted only to make it more easy for you to see the groups. In a real sheet, those columns will me unsorted. If the rank formula works right, I can sort on just the rank column and have all first place winners in each group will be followed by second place winners...and so on. I retreive those people easier.

Thanks again

 

[Mark W.]

Row order doesn't matter (I'm not sure my Aladin mentioned it in his notes). I added a random number column to the list and resorted it repeatedly based on those values and both formulas work just fine!
This message was edited by Mark W. on 2002-03-08 16:42

 

[JohnJay]

I just have one thing to say. You guys are all fantastic. Thank you so much for all your help. I think that is going to work.....I misread the copy part...ITs working now....Thanks again to you all

 

[Aladin Akyurek]

On 2002-03-08 16:30, JohnJay wrote:
I think maybe my last post misled those of you who are trying to help. I entered both versions of the formulas and got all 1's in the rank column. I sorted the data I posted only to make it more easy for you to see the groups. In a real sheet, those columns will me unsorted. If the rank formula works right, I can sort on just the rank column and have all first place winners in each group will be followed by second place winners...and so on. I retreive those people easier.

Thanks again

John,

After posting the formula, I left everything as is and gone to bed.

Indeed, forget what I said about sorting. The formula doesn't require that at all. I want to add more to what we have.

If you have definite ranges (the data do not change frequently), the formula is OK including the shortining of

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

to

ROW($1:$25)

If the range of the data is dynamic, use however the following.

I still assume the example sample to be in A1:E25.

In F1 enter:

=MATCH(9.99999999999999E+307,A:A)

In F2 enter:

="1:"&F1

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

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

This gives you a small advantage in that you don't need to edit the formula when the data changes: You only need to activate B2 and give a double-click on the little black square (fill handle) in the lower right corner of this cell.

Aladin

 

[JohnJay]

Aladin,

I read your last post and the data will be dynamic. Can you explain your last most to me. I did not quite get it.

Thanks
John

 

[Aladin Akyurek]

On 2002-03-09 14:39, JohnJay wrote:
Aladin,

I read your last post and the data will be dynamic. Can you explain your last most to me. I did not quite get it.

Thanks
John

Did you try it? Is it OFFSET that you want to know about?

Aladin

 

[JohnJay]

I tried the fromulas you and mark gave in the previous page. They both wirked great. Then I saw your next post on Dynamic data. Here is an idea of how I would like it to grow. IF I add any new columns to the sheet, the rank formula should adjust its references to still provide the correct data. However, if I add new rows to the sheet, I would like the ranks to adjust acording to the data that was entered in the new rows. I think thats what you were explaining in your last post. I was just curious about the F1 & F2 entries.

Thanks again, you guys are great help to us newbies... LOL

John

 

[JohnJay]

Aladin,

I understand what you are doing, but I had to make a modification on your Match function. I had to change it to:
=MATCH(A1,A:A) cause there are codes in Column A that can Be AlphaNumeric as well as Numeric...Sort of like last minute entries that are fitted into a slot between 122 & 123 would get a value of 122A. When I modified that code, it gave me the correct number of entries in Column A.

I think that I will stick to that and make a small field down in the bowels of the sheet to store those two formulas you added on.

Maybe what I will do is initialize & format the sheet upon load. this way all the formulas are there and read to just accept data.

Thanks again
John

 

[Aladin Akyurek]

On 2002-03-09 14:52, JohnJay wrote:
I tried the fromulas you and mark gave in the previous page. They both wirked great. Then I saw your next post on Dynamic data. Here is an idea of how I would like it to grow. IF I add any new columns to the sheet, the rank formula should adjust its references to still provide the correct data. However, if I add new rows to the sheet, I would like the ranks to adjust acording to the data that was entered in the new rows. I think thats what you were explaining in your last post. I was just curious about the F1 & F2 entries.

Thanks again, you guys are great help to us newbies... LOL

John

Both the original formula [ I'm the copyright owner ] and the dynamic version will adjust automatically when you insert additional columns within the data area. The latter needs not to be changed when you add new rows data to or delete rows from the data area, while the former must be edited to adapt to the changed data area. You just need the copy the formula down (by using the double-click trick).

F1 computes what the row number of the last cell that contains a score, which OFFSET uses to return a range reference.

F2 computes what LARGE needs.

If you need more, just shoot. And, if you like I can send you a copy of the WB.

Aladin
This message was edited by Aladin Akyurek on 2002-03-09 15:43
This message was edited by Aladin Akyurek on 2002-03-09 15:44

 

[JohnJay]

I saw that when I tired your code out. I think the latter one would be the best thing for a growing worksheet. I will make a small area in the depts of the sheet, say around IV65536, to store those formulas. It will make them safe from overwrites and accidents...LOL. But I would like to intialize a sheet on startup to contain the rank formula from row 1 all the way to row 65536...this way I dont need to use the fill button at all.

Thanks
John