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.

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.

Try this:

A1 = some number

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

--
Biff
Microsoft Excel MVP


"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.

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.

Nice one, Rick. That's the most compact version I've seen. I'll add that to
my library!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

Thanks! One possible addition you may want to consider...

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

Adding the 0 makes the formula return 0th rather than th when A1 is blank.
Unlike the formula you posted, there is no feedback error when A1 is blank,
so treating blanks like zeroes should probably be an acceptable compromise;
although I guess a standard IF(A1="","",....) wrapper is still a
possibility.

Rick


"T. Valko" wrote in message
...
Nice one, Rick. That's the most compact version I've seen. I'll add that
to my library!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

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)

That's the most compact version I've seen.

We can save another 3 characters by doing this...

=A1&MID("thstndrdth",1+2*MIN(4,MOD(A1,10))*(ABS(MO D(A1,100)-12)1),2)

but doing so adds another function call to the mix, so I'm guessing that
would make it a less desireable alternative.

Rick

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)

That's the most compact version I've seen.

By substituting your RIGHT(A1) function call in place of my MOD(A1,10)
function call, we can save one additional character...

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

although this now exposes it to same error condition that your formula does
when A1 is blank (which may considered a good thing in the end).

Rick


"T. Valko" wrote in message
...
Nice one, Rick. That's the most compact version I've seen. I'll add that
to my library!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

Making "significant" progress:

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

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

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(ABS(MOD(A1 ,100)-12)1)+1),2)

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


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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)

That's the most compact version I've seen.

By substituting your RIGHT(A1) function call in place of my MOD(A1,10)
function call, we can save one additional character...

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

although this now exposes it to same error condition that your formula
does when A1 is blank (which may considered a good thing in the end).

Rick


"T. Valko" wrote in message
...
Nice one, Rick. That's the most compact version I've seen. I'll add that
to my library!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

Making "significant" progress:

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

Yes, you did make significant progress... Nice going!

One of the problems with my coming into the Excel newsgroups armed basically
with only my knowledge of the compiled VB world is I tend to think in VB and
then translate that to either VBA or spreadsheet formulas. This means that,
when translating, I have to be aware of the differences between how
identically named functions perform. In this case, I completely forgot that
the spreadsheet version of the MOD function, unlike the VB/VBA Mod function,
never returns negative values. I needed to see your formula in order for me
to remember that fact. Had I remembered that back when I first translated my
VB Ordinal function into the spreadsheet formula I originally posted, I
would have abandoned the ABS function call (which was there solely to work
around the fact that VB's Mod function can return negative values) in favor
of the MOD(A1-11,100)2 logical expression you used (although I am sure it
would have taken me some "fooling around time" to arrive at it). Then,
progressing through the "formula shortening" process I posted earlier in
this thread, I would have ended up posting this final formula...

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

As it turns out, it is the same length as your formula above! Now, I used my
originally posted formula as its basis, which accounts for the difference in
the construction of our two formulas; although interestingly, we used all
the same functions (in different ways, of course). However, I am glad that I
didn't come up with this formula yesterday because, more than likely, you
would not have ended up posting the formula that you eventually did. Aside
from the fun and satisfaction of your doing it yourself, I (as someone who
was a math major back in college) find our two different mathematical
constructions, which end up yielding the same result, a fascinating thing to
study.

Rick

I really enjoy tinkering like this. I like the threads where we occasionally
"go deep"! We can all learn from these types of idea exchanges and it makes
us better.

I've searched the web (a few times in the past) and *every* version of this
formula (up till now) is significantly longer (and/or doesn't work for any
number!). It took your posting the idea of searching a string for the
correct position (Brilliant!) to give me a nudge.

You may be interested to know that these 2 versions had considerably
different calc times:

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

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(ABS(MOD(A1 ,100)-12)1)+1),2)

The 2nd one was much faster even though it has an additional function call.

The same basic formula can be easily modified for use on ordinal days of the
month (dates) and is *still* shorter and better than the "standards"
(including the one I've used in the past).


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Making "significant" progress:

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

Yes, you did make significant progress... Nice going!

One of the problems with my coming into the Excel newsgroups armed
basically with only my knowledge of the compiled VB world is I tend to
think in VB and then translate that to either VBA or spreadsheet formulas.
This means that, when translating, I have to be aware of the differences
between how identically named functions perform. In this case, I
completely forgot that the spreadsheet version of the MOD function, unlike
the VB/VBA Mod function, never returns negative values. I needed to see
your formula in order for me to remember that fact. Had I remembered that
back when I first translated my VB Ordinal function into the spreadsheet
formula I originally posted, I would have abandoned the ABS function call
(which was there solely to work around the fact that VB's Mod function can
return negative values) in favor of the MOD(A1-11,100)2 logical
expression you used (although I am sure it would have taken me some
"fooling around time" to arrive at it). Then, progressing through the
"formula shortening" process I posted earlier in this thread, I would have
ended up posting this final formula...

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

As it turns out, it is the same length as your formula above! Now, I used
my originally posted formula as its basis, which accounts for the
difference in the construction of our two formulas; although
interestingly, we used all the same functions (in different ways, of
course). However, I am glad that I didn't come up with this formula
yesterday because, more than likely, you would not have ended up posting
the formula that you eventually did. Aside from the fun and satisfaction
of your doing it yourself, I (as someone who was a math major back in
college) find our two different mathematical constructions, which end up
yielding the same result, a fascinating thing to study.

Rick

See inline comments....

I really enjoy tinkering like this.

I definitely got that impression.

I like the threads where we occasionally "go deep"! We can all
learn from these types of idea exchanges and it makes us better.

Agreed! In the seven plus years I have been volunteering answering newsgroup
questions... the "go deep" threads are the ones I always hope will come up.

I've searched the web (a few times in the past) and *every* version of
this formula (up till now) is significantly longer (and/or doesn't work
for any number!). It took your posting the idea of searching a string for
the correct position (Brilliant!) to give me a nudge.

Not sure about the "brilliant" part, but I am glad to have done my part in
bringing about a new solution to an old problem. The trick of using the Mid
function to perform non-linear lookups is an old one for me. I started with
conputers back in 1981, developed a sort of expertise in BASIC language
programming and use it as the basis for some dozen plus articles I had
published in various computer magazines throughout the 1980s. In January
1985, I had an article showing how to do a screen dump from a Radio Shack
Model 100 computer (the first, true laptop). From that article...

"Try as I might, I was unable to find any combination of mathematical or
logical operators which would convert the Model 100's binary code into the
mirror image required by the Epson printer. I finally settled on the MID$
function, putting the codes for the printer into a string in reverse order."

I then when on to explain how to use the MID$ function to map numerical
relationships into ordered return values. However, I had already been using
the idea behind this technique for some 3 plus years prior to the
publication of my article. Hey, do you think maybe I "invented" this
technique?<g

You may be interested to know that these 2 versions had considerably
different calc times:

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

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(ABS(MOD(A1 ,100)-12)1)+1),2)

The 2nd one was much faster even though it has an additional function
call.

I find this result very hard to believe. Look at the two formulas... with
the exception of the location of the +1, each one evaluates the
**indentical** expression, but the second one embeds it within a MIN
function call. Can the shorter main text string in the second formula really
add significantly to the efficiency of performing the MID function call that
it can compensate for the extra function call? I find it real hard to
imagine that is the case.

The same basic formula can be easily modified for use on ordinal days of
the month (dates) and is *still* shorter and better than the "standards"
(including the one I've used in the past).

Great... maybe my posting that usage for the MID function will open up a
whole new batch of more efficient (or at least more compact) replacements
for existing solutions.

Rick

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.

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.

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.

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.

Hello,

Or
=RANK(A2,A$2:A$11)&CHOOSE(MIN(4,RIGHT(RANK(A2,A$2: A$11),
1)),"st","nd","rd","th")
and copy down.

FastExcel says its 13.6 microsecs per formula (compared to Biff's one
which takes about 16.4).

Regards,
Bernd

Hello,

Why not
Function Ordinal2(Cell As Range) As String
Ordinal2 = Cell.Value & Mid("thstndrdthththththth", 1 + 2 *
Right(Cell.Value, 1), 2)
End Function
?

Regards,
Bernd

Why not
Function Ordinal2(Cell As Range) As String
Ordinal2 = Cell.Value & Mid("thstndrdthththththth", 1 + 2 *
Right(Cell.Value, 1), 2)
End Function
?

Try 111 as but one example.

Rick

Hi Rick,

Right.

Regards,
Bernd

That doesn't work for any rank that ends in 0 (10th, 20th). CHOOSE causes an
error.

Also, use it on a large range (more than 10 cells) and you'll see it returns
incorrect results for other ordinals.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

Or
=RANK(A2,A$2:A$11)&CHOOSE(MIN(4,RIGHT(RANK(A2,A$2: A$11),
1)),"st","nd","rd","th")
and copy down.

FastExcel says its 13.6 microsecs per formula (compared to Biff's one
which takes about 16.4).

Regards,
Bernd

You're welcome. Thanks for the feedback!

Here are a couple of good sites with loads of info:

http://cpearson.com/excel/mainpage.aspx

http://contextures.com/

--
Biff
Microsoft Excel MVP


"gorro" wrote in message
...
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.