| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
