Ranking

please can some one help me with a rank formula if have data like 5,8,9,1,3,0 from column A1 to A6.i want the rank to appear in B1 then i Will autofill the rest but the ranks should be in this format (1st,2nd,3rd.....) not 123... and if a cell is empty,its rank should be excluded and appears empty too.help me with a formula pls.thanks in advance

For this to work, move the values down to start at A2, put "Score" in A1. Put "Rank" in B1, then this formula in B2, copied down:

=IF($A2=0, "", RANK($A2,$A$2:$A$100)&MID("thstndrdth", MIN(9, 2 * RIGHT(RANK(A2,$A$2:$A$100))*(MOD(RANK(A2,$A$2:$A$100)-11, 100) > 2) + 1), 2))

thanks alot JB and NBVC the formula works perfectly..... keep on your good work

Do you mean, in B1:

=IF(A1="","",RANK(A1,$A$1:$A$5)&IF(OR(RIGHT(RANK(A1,$A$1:$A$5),2)+0={11,12,13}),"th",LOOKUP(RIGHT(RANK(A1,$A$1:$A$5),1)+0,{0,1,2,3,4;"th","st","nd","rd","th"})))

copied down?

The above will repeat rank if duplicate value found...

If you don't want repeat:

=IF(A1="","",RANK(A1,$A$1:$A$5)+COUNTIF($A$1:$A1,A1)-1&IF(OR(RIGHT(RANK(A1,$A$1:$A$5)+COUNTIF($A$1:$A1,A1)-1,2)+0={11,12,13}),"th",LOOKUP(RIGHT(RANK(A1,$A$1:$A$5)+COUNTIF($A$1:$A1,A1)-1,1)+0,{0,1,2,3,4;"th","st","nd","rd","th"})))

to avoid repeated calculations, you can use a helper column with just:

=IF(A1="","",RANK(A1,$A$1:$A$5)+COUNTIF($A$1:$A1,A1)-1)

in it

then in next column:

=IF(A1="","",B1&IF(OR(RIGHT(B1,2)+0={11,12,13}),"th",LOOKUP(RIGHT(B1,1)+0,{0,1,2,3,4;"th","st","nd","rd","th"})))

adds the endings.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.