1st and 2nd.... positions

[gorro]

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.

 

[Don Guillett]

From a post replying to this subject in the programming group
Re: 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

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

=0+A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(ABS(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

 

[Rick Rothstein \(MVP - VB\)]

And, because the main function is a one-liner, it lends itself to

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(MOD(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

 

[Rick Rothstein \(MVP - VB\)]

And, because the main function is a one-liner, it lends itself to

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]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[gorro]

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

 

[T. Valko]

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)

 

[T. Valko]

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

 

[gorro]

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

 

[Bernd P]

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

 

[Bernd P]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bernd P]

Hi Rick,

Right.

Regards,
Bernd