RANK Function Modified?

[Don]

I believe a need a slighly modifed version of the RANK
function.

Here is the scenario....

I have three columns A,B,C with Name, Score and Points.
Points given are based upon ranking. So, if you have the
highest score, you get the most amount of points. The
rank formula in the first relevant row is =RANK
(B2,$b$2:$b$11,1). This works nicely unless there is a
TIE in a score.

If there are 4 participants and a tie for second, the
resulting points(rankings) would be 4, 2, 2, 1. This is
not what I want. I want to distribute all the points
amongst all the tied players, so that each player in this
scenario would receive 2.5 points. I hope I am making
sense here.

There are also two things to keep in mind
(1) The scores will not necessarily be sorted, because
there are many categories to score
(2) Although unlikely, there could be more than two teams
tied, so points might need to be divided amongst three or
more teams.

Thanks for any insight anyone mihgt be able to provide....

Thanks,
Don

 

[Bob Phillips]

Don,

Assuming you have a standard RANK formula in C, then in D1 add

=(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Vasant Nanavati]

Something like:

=RANK(B2,B$2:B$11,1)+(COUNTIF(B$2:B$11,B2)*(COUNTIF(B$2:B$11,B2)-1)/2)/COUNT
IF(B$2:B$11,B2)

 

[Vasant Nanavati]

Darn, you beat me to it; Bob!

Regards,

Vasant

Don,

Assuming you have a standard RANK formula in C, then in D1 add

=(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Vasant Nanavati]

Actually, Bob; yours seems to work differently from mine, so one of us has
misunderstood the OP's requirements!

 

[Don]

I'll try both this eveining... it for my fantasy baseball
league comming up...


Thanks to both of you Bob and Vasant. I'll repost and let
you know how I make out.

 

[Bob Phillips]

Yours is right Vasant, I forgot to invert for scores.

Regards

Bob

 

[Guest]

Van...

This seems to work great! I tested it using a few
different scenarios. Thank you. I certainly understand
how countif works, but how were you able to come up with
the logic...

I'll try your scenario as soon as I get a chance Bob....

 

[Bob Phillips]

Don't bother . Vasant scored it which I failed to do.

Bob

 

[N Harkawat]

On column C type
=RANK(B2,B$2:B$11,1)+IF(1/COUNTIF($B$2:$B$11,B2)<1,1/COUNTIF($B$2:$B$11,B2),0)
will take care of situations where there are more than 2 scores that tie
If there are 3 scores ranked then the above will add 0.333 toeach of them
and so on.

 

[Vasant Nanavati]

Unfortunately, if there are 3 scores ranked the same then you would need to
add 1 to each of them (I made the same mistake in my first attempt!).

 

[Daniel.M]

Hi,

Also, this ARRAY formula:

=AVERAGE(COUNTIF(B$2:B$11,"<"&B2)+ROW(INDIRECT("1:"&COUNTIF(B$2:B$11,B2))))

Regards,

Daniel M.

 

[Daniel.M]

=AVERAGE(COUNTIF(B$2:B$11,"<"&B2)+ROW(INDIRECT("1:"&COUNTIF(B$2:B$11,B2))))

Maybe this one (not an ARRAY formula) :

=COUNTIF(B$2:B$11,"<"&B2)+(1+COUNTIF(B$2:B$11,B2))/2

Regards,

Daniel M.