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

 

[T. Valko]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(B2="","",SUM(IF(B2<B$2:B$8,1/COUNTIF(B$2:B$8,B$2:B$8)))+1)

Or, this non-array version (normally entered)

=IF(B2="","",SUMPRODUCT(--(B2<B$2:B$8),1/COUNTIF(B$2:B$8,B$2:B$8&""))+1)

Copy down

Biff

 

[orpheusgrey]

What goes in the quotation marks?

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(B2="","",SUM(IF(B2<B$2:B$8,1/COUNTIF(B$2:B$8,B$2:B$8)))+1)

Or, this non-array version (normally entered)

=IF(B2="","",SUMPRODUCT(--(B2<B$2:B$8),1/COUNTIF(B$2:B$8,B$2:B$8&""))+1)

Copy down

Biff

 

[Dave Peterson]

Nothing.

They're empty strings.

And they're used to either make the cell look empty--or to make sure empty cells
don't break the formula.

 

[orpheusgrey]

Perfect! Worked like a charm. Sorry for my noob confusion...

Thanks a lot
B

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Perfect! Worked like a charm. Sorry for my noob confusion...

Thanks a lot
B