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