Reverse range...with a zero

Array-enter (that is, hit control+shift+enter to enter)

D40 =IF(ROW()-ROW($D$40:$D$45)+1>ROWS($C$40:$C$45)-COUNTIF($C$40:$C$45,0),"",INDIRECT(ADDRESS(SMALL((IF($C$40:$C$45<>0,ROW($C$40:$C$45),ROW()+ROWS($C$40:$C$45))),ROW()-ROW($D$40:$D$45)+1),COLUMN($C$40:$C$45),4)))

Copy the above formula to D41:D45.

Enter

E1 =COUNT(D$40:D$45)-(RANK(D40,D$40:D$45)+COUNTIF(D$40:D40,D40))+2

This is your formula for ranking. Copy down this as far as needed.

Enter

F1 =RANK(D40,$D$40:$D$45,1)

Copy down this as far as needed. This formula assigns the same rank to the identical game scores.

Enter

G1 =RANK(D40,D$40:D$45,1)+COUNTIF(D$40:D40,D40)-1

Copy down this formula (which I owe to Celia who is a regular contributor at this board) as far as needed.

Compare the results in E, F, and G. You see that the formula in G delivers ranks identical to yours. The long and the short is that formulas in E and G treat the identical scores as if they are different. The one in F (as said before), on the other hand, assigns the same rank to the scores that are equal to each other.

Aladin

ok....succesful with this
=IF(ROW()-ROW($C$47:$C$52)+1>ROWS($C$40:$C$45)-COUNTIF($C$40:$C$45,0),"", INDIRECT(ADDRESS(SMALL((IF($C$40:$C$45<>0,ROW($C$40:$C$45),ROW()+ROWS ($C$40:$C$45))),ROW()-ROW($C$47:$C$52)+1),COLUMN($C$40:$C$45),4))) 

copied down from from c47 to c52 here are the results
37
79
93
146
116
blank

then I rank
=RANK(C47,$C$47:$C$52,1)
copied down to c53 to c58
my results are
1
2
3
5
4
#VALUE!
the next step i want to go is to
match up the people with their rank
so i try this
=OFFSET(B$40,MATCH(SMALL(C$53:C$58,ROW()-ROW (C$59)+1),C$53:C$58,0)-1,0) 

it returns #VALUE!

the 0 in the people's scores can
and will occur often...so to get to my final
destination the cells that come up value...have
to be ignored some how

thanks for your help and you had
mentioned Celia....thanks then to her
patrick

[ snip ]

Hi Patrick,

Watch out for the ranges when setting up the formulas.

I understand that you will have a lot of zeros and maybe blanks in your column for scores. You may also have multiple entries for the same player. If so, I propose to start over.

I'll assume that

B40:B49 contains the names of the players;
C40:C49 contains scores including zeros and blanks.

Enter the following formulas:

D40 =IF(ISNUMBER(C40),IF(C40>0,C40&"-"&B40,0),0) [ copy down as far as needed ]

E40 =IF(ROW()-ROW($E$40:$E$49)+1>ROWS($D$40:$D$49)-COUNTIF($D$40:$D$49,0)-COUNTBLANK($D$40:$D$49),"",INDIRECT(ADDRESS(SMALL((IF($D$40:$D$49<>0,ROW($D$40:$D$49),ROW()+ROWS($D$40:$D$49))),ROW()-ROW($E$40:$E$49)+1),COLUMN($D$40:$D$49),4))) 

[ this must be array-entered and copied down as far as needed ]

F40 =IF(LEN(E40)>0,VALUE(MID(E40,1,FIND("-",E40)-1)),"") [ copy down as far as needed ]

G40 =IF(ISNUMBER(F40),RANK(F40,$F$40:$F$49,1),"") [ copy down as far as needed ]

H40 =IF(ISNUMBER(G40),MID(E40,FIND("-",E40)+1,LEN(E40)-FIND("-",E40)+1),"") [ copy down as far as needed ]

The latter gives you the names of the players whose scores are ranked.

This took some time to cook up. Hope it does what you want.

Note. You'll get the related excel file thru e-mail.

Aladin

thanks again.......works great
finally finished the project.....without
your help......it would not have been done..
.appreciate the help.....