rank question

[orpheusgrey]

Okay, I'm sure this question has been answered here before. But I
can't find the exact answer to my question in the hundreds of posts
that pop up at my keyword search.

I've made a list for a fantasy movie league. It's just like fantasy
sports. Anyway, I am trying to rank the 7 players in our league
according to the points they have scored. I am having the common
problem of the Rank function duplicating ranks for ties, then skipping
the next rank #.

I want to keep the duplicated ranks. If two players each have 50pts, I
want them to both have the same rank. BUT, I don't want the players
with 40pts to be ranked one number less.

This is what I want things to look like, to be clear-

PLAYER PTS RANK
Jay 70 1
Lou 60 2
May 50 3
Art 40 4
Bev 40 4
Dee 30 5
Joe 20 6

Now, my list ranks Dee as 6th, and Joe as 7th.
Any ideas what formula I need to fix this?
The ranking formula I am using is- =RANK(D6, D$2$8)

Thanks
B

 

[Bob Phillips]

=COUNT($B$2:$B$8)-(SUM(IF(B2>$B$2:$B$8,1/COUNTIF($B$2:$B$8,$B$2:$B$8)))+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)