Postions - 1st 2nd 3rd 4th etc
Author Message
Postions - 1st 2nd 3rd 4th etc

I using Excel to sort the exam marks of school pupils and also prints a
column that gives the position of each pupil.
EG
EXAM %            POSITION
99                            1st
90                            2nd
85                            3rd
85                            3rd
81                            5th

Notice the actual position letters - st, nd, rd, th, etc.
I have written a Visual Basic function that will do this, but it slows down
the calculations when sorting hundreds of marks.

Does anyone know of a commercial/other add-in function that will produce
these position letters?

Richard

Wed, 18 Jun 1902 08:00:00 GMT
Postions - 1st 2nd 3rd 4th etc
Richard,

The formula

=IF(OR(MOD(A1,10)+1>=5,AND(MOD(A1,100)>10,MOD(A1,100)<19)),"th",CHOOSE(MOD(A
1,10)+1,"th","st","nd","rd"))

will return the correct suffix for any number in A1.  It will properly
return "th" for all the {*filter*} numbers (e.g., 13 is 13th, not 13rd).   You can
then use this with any other formula.  To get the rankings for the numbers
in A1:A15,  enter the formula

=RANK(A1,\$A\$1:\$A\$15)&IF(OR(MOD(RANK(A1,\$A\$1:\$A\$15),10)+1>=5,AND(MOD(RANK(A1,
\$A\$1:\$A\$15),100)>10,MOD(RANK(A1,\$A\$1:\$A\$15),100)<19)),"th",CHOOSE(MOD(RANK(A
1,\$A\$1:\$A\$15),10)+1,"th","st","nd","rd"))

In A1, and fill down to A15.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services

Quote:
> I using Excel to sort the exam marks of school pupils and also prints a
> column that gives the position of each pupil.
> EG
> EXAM %            POSITION
> 99                            1st
> 90                            2nd
> 85                            3rd
> 85                            3rd
> 81                            5th

> Notice the actual position letters - st, nd, rd, th, etc.
> I have written a Visual Basic function that will do this, but it slows
down
> the calculations when sorting hundreds of marks.

> Does anyone know of a commercial/other add-in function that will produce
> these position letters?

> Richard

Wed, 18 Jun 1902 08:00:00 GMT
Postions - 1st 2nd 3rd 4th etc
Hi Chip,

I know, its fussy, but wouldnt MOD(A1,10)>=4 instead of  MOD(A1,10)+1>=5
be two letters shorter? (Wow!!! Two letters!!!)
Or is there any case in which the two formulas have different results?

Michael

Quote:
> Richard,

> The formula

=IF(OR(MOD(A1,10)+1>=5,AND(MOD(A1,100)>10,MOD(A1,100)<19)),"th",CHOOSE(MOD(A
Quote:
> 1,10)+1,"th","st","nd","rd"))

> will return the correct suffix for any number in A1.  It will properly
> return "th" for all the {*filter*} numbers (e.g., 13 is 13th, not 13rd).   You
can
> then use this with any other formula.  To get the rankings for the numbers
> in A1:A15,  enter the formula

=RANK(A1,\$A\$1:\$A\$15)&IF(OR(MOD(RANK(A1,\$A\$1:\$A\$15),10)+1>=5,AND(MOD(RANK(A1,
\$A\$1:\$A\$15),100)>10,MOD(RANK(A1,\$A\$1:\$A\$15),100)<19)),"th",CHOOSE(MOD(RANK(A
Quote:
> 1,\$A\$1:\$A\$15),10)+1,"th","st","nd","rd"))

> In A1, and fill down to A15.

> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting Services

> > I using Excel to sort the exam marks of school pupils and also prints a
> > column that gives the position of each pupil.
> > EG
> > EXAM %            POSITION
> > 99                            1st
> > 90                            2nd
> > 85                            3rd
> > 85                            3rd
> > 81                            5th

> > Notice the actual position letters - st, nd, rd, th, etc.
> > I have written a Visual Basic function that will do this, but it slows
> down
> > the calculations when sorting hundreds of marks.

> > Does anyone know of a commercial/other add-in function that will produce
> > these position letters?

> > Thanks for your help.

> > Richard

Wed, 18 Jun 1902 08:00:00 GMT
Postions - 1st 2nd 3rd 4th etc
The formula works great!!!!

Richard

Quote:
> Richard,

> The formula

=IF(OR(MOD(A1,10)+1>=5,AND(MOD(A1,100)>10,MOD(A1,100)<19)),"th",CHOOSE(MOD(A
Quote:
> 1,10)+1,"th","st","nd","rd"))

> will return the correct suffix for any number in A1.  It will properly
> return "th" for all the {*filter*} numbers (e.g., 13 is 13th, not 13rd).   You
can
> then use this with any other formula.  To get the rankings for the numbers
> in A1:A15,  enter the formula

=RANK(A1,\$A\$1:\$A\$15)&IF(OR(MOD(RANK(A1,\$A\$1:\$A\$15),10)+1>=5,AND(MOD(RANK(A1,
\$A\$1:\$A\$15),100)>10,MOD(RANK(A1,\$A\$1:\$A\$15),100)<19)),"th",CHOOSE(MOD(RANK(A
Quote:
> 1,\$A\$1:\$A\$15),10)+1,"th","st","nd","rd"))

> In A1, and fill down to A15.

> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting Services

> > I using Excel to sort the exam marks of school pupils and also prints a
> > column that gives the position of each pupil.
> > EG
> > EXAM %            POSITION
> > 99                            1st
> > 90                            2nd
> > 85                            3rd
> > 85                            3rd
> > 81                            5th

> > Notice the actual position letters - st, nd, rd, th, etc.
> > I have written a Visual Basic function that will do this, but it slows
> down
> > the calculations when sorting hundreds of marks.

> > Does anyone know of a commercial/other add-in function that will produce
> > these position letters?

> > Thanks for your help.

> > Richard

Wed, 18 Jun 1902 08:00:00 GMT
Postions - 1st 2nd 3rd 4th etc
Michael,

Yeah, it is shorter.  I had just cut and pasted the MOD() from the CHOOSE
portion of the formula.  The result should be identical.  Save another
character with MOD(A1,10)>3

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services

Quote:
> Hi Chip,

> I know, its fussy, but wouldnt MOD(A1,10)>=4 instead of  MOD(A1,10)+1>=5
> be two letters shorter? (Wow!!! Two letters!!!)
> Or is there any case in which the two formulas have different results?

> Michael

> > Richard,

> > The formula

=IF(OR(MOD(A1,10)+1>=5,AND(MOD(A1,100)>10,MOD(A1,100)<19)),"th",CHOOSE(MOD(A
Quote:
> > 1,10)+1,"th","st","nd","rd"))

> > will return the correct suffix for any number in A1.  It will properly
> > return "th" for all the {*filter*} numbers (e.g., 13 is 13th, not 13rd).   You
> can
> > then use this with any other formula.  To get the rankings for the
numbers
> > in A1:A15,  enter the formula

=RANK(A1,\$A\$1:\$A\$15)&IF(OR(MOD(RANK(A1,\$A\$1:\$A\$15),10)+1>=5,AND(MOD(RANK(A1,
\$A\$1:\$A\$15),100)>10,MOD(RANK(A1,\$A\$1:\$A\$15),100)<19)),"th",CHOOSE(MOD(RANK(A

- Show quoted text -

Quote:
> > 1,\$A\$1:\$A\$15),10)+1,"th","st","nd","rd"))

> > In A1, and fill down to A15.

> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting Services

> > > I using Excel to sort the exam marks of school pupils and also prints
a
> > > column that gives the position of each pupil.
> > > EG
> > > EXAM %            POSITION
> > > 99                            1st
> > > 90                            2nd
> > > 85                            3rd
> > > 85                            3rd
> > > 81                            5th

> > > Notice the actual position letters - st, nd, rd, th, etc.
> > > I have written a Visual Basic function that will do this, but it slows
> > down
> > > the calculations when sorting hundreds of marks.

> > > Does anyone know of a commercial/other add-in function that will
produce
> > > these position letters?

> > > Thanks for your help.

> > > Richard

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages