Solved HOW TO NUMBER RANK SOLUTIONS 1st 2nd 3rd etc

September 21, 2014 at 16:17:46
Specs: Windows 7
Thank you for you replies.
I have no problem with the Rank function, however, instead of the results being shown as 1, 2, 3 etc., i would like them to be recorded as 1st, 2nd, 3rd etc. Is there anyway i can have the "st", "nd", "rd", "th" included with the numerical result.

See More: HOW TO NUMBER RANK SOLUTIONS 1st 2nd 3rd etc

Report •


#1
September 21, 2014 at 17:00:54
Why did you start a new thread, instead of simply replying to your existing question?

It makes following the discussion difficult.

As to your question of adding the ordinal suffix to your numbers, try this:

=(RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1)&" "&IF(AND(MOD(ABS(B1),100)>=10,MOD(ABS(B1),100)<=14),"th",CHOOSE(MOD(ABS(B1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

It is a fairly long formula, so you should probably do a copy & paste.

OK, now I'm confused.

This goes in column B:

=RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1

This goes in column C:

=B1&" "&IF(AND(MOD(ABS(B1),100)>=10,MOD(ABS(B1),100)<=14),"th",CHOOSE(MOD(ABS(B1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

so you have:

     A   B   C
 1) 33  10  10 th
 2) 45  9   9 th
 3) 46  8   8 th
 4) 49  7   7 th
 5) 60  5   5 th
 6) 60  6   6 th
 7) 68  4   4 th
 8) 69  3   3 rd
 9) 85  2   2 nd
10) 97  1   1 st

Tried to combine the formulas, but could not make them work,


DerbyDad03
,
thanks for the heads up, I have a few different formulas on my sheet
and copied the wrong one.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
September 21, 2014 at 17:03:19
Mike,

Why the use of B1 in your formula?

Don't you need the RANK function in place of B1?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
September 21, 2014 at 17:57:50
✔ Best Answer
This seems to work...

=RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1&
IF(AND(MOD(ABS(RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1),100)>=10,
MOD(ABS(RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1),100)<=14),"th", CHOOSE(MOD(ABS(RANK(A1,A$1:A$10)+COUNTIF(A$1:A1,A1)-1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 21, 2014 at 18:02:46
DerbyDad03,

you beat me to it, I was just going to post the same thing. :-)

MIKE

http://www.skeptic.com/


Report •

#5
September 21, 2014 at 20:07:27
Cheers works perfectly, much appreciated. If i want to rank A1:A150, what do i need to do.

Report •

#6
September 21, 2014 at 21:18:39
Just change all the references from A$1:A$10 to A$1:A$150 and drag it down.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
September 21, 2014 at 22:31:50
Cheers. Cannot work out how to include duplicate rankings. Sorry but i'm new at this

Report •

#8
September 22, 2014 at 03:40:31
Why did you say it works perfectly and then say you can't work out how to include duplicate rankings? I thought you didn't want to include duplicate rankings?

I'm confused. Have we solved your issue or not? You marked the thread as solved and then asked another question. Now you appear to still have an issue.

Please explain the status of this issue. Is it solved or not? If not,what exactly is your current issue?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
September 22, 2014 at 06:28:46
It did work perfectly until I realized I needed to have duplicate rankings

Report •

#10
September 22, 2014 at 06:42:50
Just delete the COUNTIF() section:

=RANK(A1,A$1:A$10)&" "&IF(AND(MOD(ABS(RANK(A1,A$1:A$10)),100)>=10,MOD(ABS(RANK(A1,A$1:A$10)),100)<=14),"th", CHOOSE(MOD(ABS(RANK(A1,A$1:A$10)),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

See how that works.

MIKE

http://www.skeptic.com/


Report •

#11
September 22, 2014 at 07:20:55
Great :):) thank you so much. Sorry about all the confusion and trouble. Shouldn't have to bother you any more.
Regards Chris

Report •

#12
September 22, 2014 at 12:37:09
Don't worry about bothering us, that's kind of what we're here for.

However, there are a couple of very important things to keep in mind when requesting assistance in a Help forum:

1 - We can't see your spreadsheet from where we're sitting; and
2 - We can't read minds

You need to provide enough detail regarding your issue so that we understand the problem we are trying to solve. Many times some "before and after" example data helps. e.g. I have this...I want this...

Each forum has different posting methods, but if you are going to post example data in this forum, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question