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?

Thanks for your help.

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?

> Thanks for your help.

> 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
Thanks for your help.
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  
 
 [ 5 post ] 

 Relevant Pages 

1. Return 1st,2nd,3rd,4th from Date

2. Displaying Day as 1st, 2nd, 3rd, etc.

3. Microsof publisher How to print 2nd 3rd and 4th quarter of page

4. Get 1st, 2nd, and 3rd Letter From a String

5. Displaying: date: 1st, 2nd, 3rd, 11th, 21st

6. 1st box<2nd box<3rd box?

7. 1st month, 2nd month, 3rd month...

8. If 1st cell is empty, I want data from 2nd cell to copy into 3rd cell

9. 1st box<2nd box etc, In forms

10. Calendar control 1st tuesday, 2nd thursday etc.


 
Powered by phpBB® Forum Software © phpBB Group