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.

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 stTried 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

message edited by mmcconaghy

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

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.

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

MIKE

Cheers works perfectly, much appreciated. If i want to rank A1:A150, what do i need to do.

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.

Cheers. Cannot work out how to include duplicate rankings. Sorry but i'm new at this

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

It did work perfectly until I realized I needed to have duplicate rankings

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

Great :):) thank you so much. Sorry about all the confusion and trouble. Shouldn't have to bother you any more.

Regards Chris

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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History