Using Rank with ties and spliting the differance

[assws]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Domenic]

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 <[email protected]>,
assws <[email protected]> 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

[Andre Croteau]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Domenic]

Nice! Definitely much more efficient!

Cheers!

In article <[email protected]>,
"Andre Croteau" <[email protected]> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >

[Bob Phillips]

That's better!

Bob


"Andre Croteau" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

[assws]

Thanks to all for the advice