Thanks a lot. That was ok. Could you pls direct me to sites i can get good
materials to learn these things? Gorro

"T. Valko" wrote:

Or, you can use a separate column for the rank and another column for the
odinal:

B2: =RANK(A2,A$2:A$11)

Copied down

C2:

=B2&MID("thstndrdth",MIN(9,2*RIGHT(B2)*(MOD(B2-11,100)2)+1),2)

Copied down

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
To combine a rank (standard Excel RANK) and the ordinal together:

Assume the numbers to rank are in the range A2:A11. Also assuming that
there is nothing in the range but numbers. No empty cells, no text, no
errors, no booleans, no formula blanks...

Enter this formula in B2 and copy down:

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

--
Biff
Microsoft Excel MVP


"gorro" wrote in message
...
You guys are dangerously good! it worked out perfectly. Only that i cant
understand how you got to that. I'm grateful.

Another small problem comes up again! That is: is it possible to get in
another column, 1st, for the highest mark, 2nd, for the next, ........

Thaks. With you, no fears.
gorro

"Rick Rothstein (MVP - VB)" wrote:

And, because the main function is a one-liner, it lends itself to
translation into a spreadsheet formula (should that be something the OP
would want)...

=A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(ABS (MOD(A1,100)-12)1),2)

Rick


"Don Guillett" wrote in message
...
From a post replying to this subject in the programming group
A #NAME? problem with a transferred function

By the way, you might find this one-liner Ordinal function I developed
back
in the compiled VB world (modified for use in Excel) interesting...

Function Ordinal(Cell As Range) As String
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", _
1 - 2 * ((Cell.Value) Mod 10) * _
(Abs((Cell.Value) Mod 100 - 12) 1), 2)
End Function

Rick


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gorro" wrote in message
...
assuming i want to arrange student positions, how do i get excel to
automatically add say st, nd, rd, th, to 1st, 2nd, 3rd, and 4th
positions?
thaks for answering.