Using Rank with ties and spliting the differance
 

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile: http://www.excelforum.com/member.php...fo&userid=4929
View this thread: http://www.excelforum.com/showthread...hreadid=390916

I am sure that this can be improved upon, but here is one way

=(RANK(B1,$B$1:$B$40,1)*COUNTIF($B$1:$B$40,B1)+IF( COUNTIF($B$1:$B$40,B1)1,S
UMPRODUCT(ROW(INDIRECT("1:"&COUNTIF($B$1:$B$40,B1)-1))),0))/COUNTIF($B$1:$B$
40,B1)

--

HTH

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


"assws" wrote in
message ...

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile:
http://www.excelforum.com/member.php...fo&userid=4929
View this thread: http://www.excelforum.com/showthread...hreadid=390916

Here's another way...

C1, copied down:

=AVERAGE(IF($B$1:$B$10=B1,(COUNTIF($B$1:$B$10,"<"& B1)+1)+(COUNTIF(OFFSET(
$B$1:$B$10,0,0,ROW($B$1:$B$10)-ROW($B$1)+1),B1)-1)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
assws wrote:

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance

Hi,

Another solution could be done this way... In cell C1

=RANK(B1,$B$1:$B$40,1)+(COUNTIF($B$1:$B$40,B1)<1) *((COUNTIF($B$1:$B$40,B1)-
1)/2)

André

"assws" wrote in
message ...

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile:
http://www.excelforum.com/member.php...fo&userid=4929
View this thread: http://www.excelforum.com/showthread...hreadid=390916

Nice! Definitely much more efficient!

Cheers!

In article ,
"Andre Croteau" wrote:

Hi,

Another solution could be done this way... In cell C1

=RANK(B1,$B$1:$B$40,1)+(COUNTIF($B$1:$B$40,B1)<1) *((COUNTIF($B$1:$B$40,B1)-
1)/2)

André

"assws" wrote in
message ...

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile:
http://www.excelforum.com/member.php...fo&userid=4929
View this thread: http://www.excelforum.com/showthread...hreadid=390916

That's better!

Bob


"Andre Croteau" wrote in message
...

Hi,

Another solution could be done this way... In cell C1


=RANK(B1,$B$1:$B$40,1)+(COUNTIF($B$1:$B$40,B1)<1) *((COUNTIF($B$1:$B$40,B1)-
1)/2)

André

"assws" wrote in
message ...

Good Morning,

The data I have looks like this

Bob 193
Scott 193
Paul 204
Ringo 149
JohnL 148
George 172
Pete 148
Rodger 148
Keith 168
JohnE 118

Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie
I want to get the average of the 2 values of the tie. So if the 2nd
place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to
allow for a three (or more) way tie for example the 7th, 8th and 9th is
a tie so (2+3+4)/3 = 3

result What I want
Bob 8 *8.5*
Scott 8 *8.5*
Paul 10 10
Ringo 5 5
JohnL 2 *3*
George 7 7
Pete 2 *3*
Rodger 2 *3*
Keith 6 6
JohnE 1 1

Thanks in advance


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile:
http://www.excelforum.com/member.php...fo&userid=4929
View this thread:
http://www.excelforum.com/showthread...hreadid=390916

Thanks to all for the advice


--
assws

Married w/ 1.5 Kid
------------------------------------------------------------------------
assws's Profile: http://www.excelforum.com/member.php...fo&userid=4929
View this thread: http://www.excelforum.com/showthread...hreadid=390916