PDA

View Full Version : Solved: RANK (??)



Hoopsah
08-19-2009, 05:26 AM
Hi

if I have a number in each cell going across 5 columns
A1 = 1
A2 = 2
A3 = 3.............

Does anyone know of a macro that will rank the numbers and display in cells A2, B2, C2 etc

so that in Cell A2 it would say "Third" (As it is the 3rd highest number)
in cell B2 it would say "Second" & (Second highest number)
Cell C2 would say "First" (Highest number)

I haven't even thought about if the cells had an equal number in them :banghead:

Any help appreciated

Hoopsah

Bob Phillips
08-19-2009, 06:05 AM
Try

=RANK(A1,$A1:$F1)&CHOOSE(AND(RANK(A1,$A1:$F1)<>{11,12,13})*MIN(4,MOD(RANK(A1,$A1:$F1),10))+1,"th","st","nd","rd","th")

Hoopsah
08-19-2009, 06:15 AM
Works perfectly.

What a formula though!!!

Thanks for your help (Again) Bob

Gerry