1st and 2nd.... positions

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
dguil...@austin.rr.com
"gorro" <go...@discussions.microsoft.com> wrote in message
news:3DE1DF64-0FA0-4809...@microsoft.com...

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

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

Rick

"Don Guillett" <dguil...@austin.rr.com> wrote in message
news:%23v7Dj%23kRIH...@TK2MSFTNGP05.phx.gbl...

"Rick Rothstein (MVP - VB)" <rickNOS...@NOSPAMcomcast.net> wrote in
message news:eKDjXxmR...@TK2MSFTNGP06.phx.gbl...

=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

"T. Valko" <biffi...@comcast.net> wrote in message
news:uLnipPnR...@TK2MSFTNGP04.phx.gbl...

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

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

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

message news:eYn7qvnR...@TK2MSFTNGP02.phx.gbl...

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

message news:ulXmNh4R...@TK2MSFTNGP03.phx.gbl...

I definitely got that impression.

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.

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>

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.

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

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

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)

news:62352F07-C713-437A...@microsoft.com...

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

news:ujiWoNXU...@TK2MSFTNGP06.phx.gbl...

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

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

Try 111 as but one example.

Rick

Right.

Regards,
Bernd

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

"Bernd P" <bplu...@gmail.com> wrote in message
news:3b48eca8-7593-43cd...@k2g2000hse.googlegroups.com...

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

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

http://contextures.com/

news:8DA98FC3-843A-4D15...@microsoft.com...