Ranking

[ENEMALI]

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

[JBeaucaire]

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))

[NBVC]

Do you mean, in B1:

Please Login or Register  to view this content.

copied down?

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

If you don't want repeat:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

in it

then in next column:

Please Login or Register  to view this content.

adds the endings.

[ENEMALI]

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

[JBeaucaire]

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