Ranking problem

[SOS]

Any help would be appreciated:

I have a spreadsheet with a list of names and points awarded to the
and have some VBA code which then ranks the names in order.

My problem is that if two people have the same number of points Exce
ranks both them as 1st place and then the next person as 3rd. I woul
like the ranking to show that after a tie in 1st the next person woul
be ranked as 2nd (or a tie in 4th the next person would be ranked 5t
(and not 6th) and so on.

Is this possible?

Many thanks

Seamu

 

[Leo Heuser]

Seamus

One way assuming data in C2:C100

In e.g. D2 enter:

=RANK(C2,$C$2:$C$100)+COUNTIF($C$2:C2,C2)-1

Notice the use of mixed absolute ($$) and relative
(No cash) references.

Copy down as far as needed.

 

[SOS]

Leo,

THanks for your reply - (I don't yet know how to post replies direct t
the newsgroup).

I tried the formula you suggested but it didn't rank the cells the wa
I wanted: ie

I placed data in C2:C11 with points from 10, 9, 8 etc through to 1
I put the formula as suggested in D2 and copied down to D10.
Ranking shows, as expected, from 1 through 10.

I then made cell C3 have 10 points as well (indicating a tie in 1s
place) and the rankings didn't change - they remained rank 1 throug
10.

Any other suggestions

Many thanks

Seamu

 

[Leo Heuser]

Seamus

Leo,

THanks for your reply - (I don't yet know how to post replies direct to
the newsgroup).

I tried the formula you suggested but it didn't rank the cells the way
I wanted: ie

I placed data in C2:C11 with points from 10, 9, 8 etc through to 1
I put the formula as suggested in D2 and copied down to D10.
Ranking shows, as expected, from 1 through 10.

I then made cell C3 have 10 points as well (indicating a tie in 1st
place) and the rankings didn't change - they remained rank 1 through
10.

Any other suggestions

Many thanks

Seamus

What did you expect?
If you enter in your original series e.g. 10 in C8, D8 will display 2
instead of D2 and D8 both showing 1. Wasn't that, what you
were after?

BTW your answer reached the NG

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

 

[SOS]

Leo,

What I was really after was that the rankings would show the peopl
with 10 points each to be shown as Rank 1 but that the person with th
next highest points would be shown as Rank 2(not Rank 3).

Thanks again

Seamu

 

[Leo Heuser]

Hi again

OK.
This one seems to do the job.
Assuming data in A1:A10 enter this array formula
in e.g. B1:

=RANK(A1,$A$1:$A$10)-SUM(IF(ROW($A$1:$A$10)<RANK(A1,$A$1:$A$10),
LOOKUP(ROW($A$1:$A$10),ROW($A$1:$A$10),FREQUENCY(LARGE($A$1:$A$10,
ROW($A$1:$A$10)),LARGE($A$1:$A$10,ROW($A$1:$A$10)))=0)+0))

The formula must be entered with <Shift><Ctrl><Enter> instead of
<Enter>, also if edited later. If done correctly, Excel will display
the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

Copy B1 down to B10 with the fill handle (the little square in the
lower right corner of the cell)

 

[Leo Heuser]

Better use this version.
The former version only worked, if your
data started in row 1
(Assuming list in A2:A11)


=RANK(A2,$A$2:$A$11)-SUM(IF(ROW($A$2:$A$11)-ROW($A$2)+1<RANK(A2,$A$2:$A$11),
LOOKUP(ROW($A$2:$A$11)-ROW($A$2)+1,ROW($A$2:$A$11)-ROW($A$2)+1,FREQUENCY(
LARGE($A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),LARGE($A$2:$A$11,
ROW($A$2:$A$11)-ROW($A$2)+1))=0)+0))

again to be entered with <Shift><Ctrl><Enter>

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hi again

OK.
This one seems to do the job.
Assuming data in A1:A10 enter this array formula
in e.g. B1:

=RANK(A1,$A$1:$A$10)-SUM(IF(ROW($A$1:$A$10)<RANK(A1,$A$1:$A$10),
LOOKUP(ROW($A$1:$A$10),ROW($A$1:$A$10),FREQUENCY(LARGE($A$1:$A$10,
ROW($A$1:$A$10)),LARGE($A$1:$A$10,ROW($A$1:$A$10)))=0)+0))

The formula must be entered with <Shift><Ctrl><Enter> instead of
<Enter>, also if edited later. If done correctly, Excel will display
the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

Copy B1 down to B10 with the fill handle (the little square in the
lower right corner of the cell)

 

[SOS]

Leo,

Many thanks for your input. That formula works an absolute treat.

Now all I have to do is to work it into the VBA code in my project.

Excellent

Seamu

 

[Leo Heuser]

You're welcome, Seamus, and thank
you for the feedback.

LeoH