Rank question
 

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:D$8)

Thanks
B

Rank question
 

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" wrote in message
oups.com...
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:D$8)

Thanks
B

Rank question
 

What goes in the quotation marks?

On Mar 8, 4:22 pm, "T. Valko" wrote:
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" wrote in message

oups.com...

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:D$8)

Thanks
B

Rank question
 

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 wrote:

What goes in the quotation marks?

On Mar 8, 4:22 pm, "T. Valko" wrote:
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" wrote in message

oups.com...

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:D$8)

Thanks
B

--

Dave Peterson

Rank question
 

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

Thanks a lot
B


On Mar 8, 10:45 pm, Dave Peterson wrote:
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 wrote:

What goes in the quotation marks?

On Mar 8, 4:22 pm, "T. Valko" wrote:
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" wrote in message

roups.com...

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:D$8)

Thanks
B

--

Dave Peterson

Rank question
 

You're welcome. Thanks for the feedback!

Biff

"orpheusgrey" wrote in message
ps.com...
Perfect! Worked like a charm. Sorry for my noob confusion...

Thanks a lot
B


On Mar 8, 10:45 pm, Dave Peterson wrote:
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 wrote:

What goes in the quotation marks?

On Mar 8, 4:22 pm, "T. Valko" wrote:
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" wrote in message

roups.com...

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:D$8)

Thanks
B

--

Dave Peterson