|
RANK Function Modified?
I believe a need a slighly modifed version of the RANK
function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Don,
Assuming you have a standard RANK formula in C, then in D1 add =(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1) -- HTH RP (remove nothere from the email address if mailing direct) "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Something like:
=RANK(B2,B$2:B$11,1)+(COUNTIF(B$2:B$11,B2)*(COUNTI F(B$2:B$11,B2)-1)/2)/COUNT IF(B$2:B$11,B2) -- Vasant "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Darn, you beat me to it; Bob!
Regards, Vasant "Bob Phillips" wrote in message ... Don, Assuming you have a standard RANK formula in C, then in D1 add =(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1) -- HTH RP (remove nothere from the email address if mailing direct) "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Actually, Bob; yours seems to work differently from mine, so one of us has
misunderstood the OP's requirements! :) -- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Darn, you beat me to it; Bob! Regards, Vasant "Bob Phillips" wrote in message ... Don, Assuming you have a standard RANK formula in C, then in D1 add =(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1) -- HTH RP (remove nothere from the email address if mailing direct) "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
I'll try both this eveining... it for my fantasy baseball
league comming up... Thanks to both of you Bob and Vasant. I'll repost and let you know how I make out. -----Original Message----- Something like: =RANK(B2,B$2:B$11,1)+(COUNTIF(B$2:B$11,B2)*(COUNT IF (B$2:B$11,B2)-1)/2)/COUNT IF(B$2:B$11,B2) -- Vasant "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don . |
Yours is right Vasant, I forgot to invert for scores.
Regards Bob "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Actually, Bob; yours seems to work differently from mine, so one of us has misunderstood the OP's requirements! :) -- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Darn, you beat me to it; Bob! Regards, Vasant "Bob Phillips" wrote in message ... Don, Assuming you have a standard RANK formula in C, then in D1 add =(C1*COUNTIF($C$1:$C$11,C1)+COUNTIF($C$1:$C$11,C1)-1)/COUNTIF($C$1:$C$11,C1) -- HTH RP (remove nothere from the email address if mailing direct) "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
|
Van...
This seems to work great! I tested it using a few different scenarios. Thank you. I certainly understand how countif works, but how were you able to come up with the logic... I'll try your scenario as soon as I get a chance Bob.... -----Original Message----- Something like: =RANK(B2,B$2:B$11,1)+(COUNTIF(B$2:B$11,B2)*(COUNT IF (B$2:B$11,B2)-1)/2)/COUNT IF(B$2:B$11,B2) -- Vasant "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don . |
Don't bother :-). Vasant scored it which I failed to do.
Bob wrote in message ... Van... This seems to work great! I tested it using a few different scenarios. Thank you. I certainly understand how countif works, but how were you able to come up with the logic... I'll try your scenario as soon as I get a chance Bob.... -----Original Message----- Something like: =RANK(B2,B$2:B$11,1)+(COUNTIF(B$2:B$11,B2)*(COUNT IF (B$2:B$11,B2)-1)/2)/COUNT IF(B$2:B$11,B2) -- Vasant "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don . |
On column C type
=RANK(B2,B$2:B$11,1)+IF(1/COUNTIF($B$2:$B$11,B2)<1,1/COUNTIF($B$2:$B$11,B2),0) will take care of situations where there are more than 2 scores that tie If there are 3 scores ranked then the above will add 0.333 toeach of them and so on. "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Unfortunately, if there are 3 scores ranked the same then you would need to
add 1 to each of them (I made the same mistake in my first attempt!). -- Vasant "N Harkawat" wrote in message ... On column C type =RANK(B2,B$2:B$11,1)+IF(1/COUNTIF($B$2:$B$11,B2)<1,1/COUNTIF($B$2:$B$11,B2), 0) will take care of situations where there are more than 2 scores that tie If there are 3 scores ranked then the above will add 0.333 toeach of them and so on. "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
Hi,
Also, this ARRAY formula: =AVERAGE(COUNTIF(B$2:B$11,"<"&B2)+ROW(INDIRECT("1: "&COUNTIF(B$2:B$11,B2)))) Regards, Daniel M. "Don" wrote in message ... I believe a need a slighly modifed version of the RANK function. Here is the scenario.... I have three columns A,B,C with Name, Score and Points. Points given are based upon ranking. So, if you have the highest score, you get the most amount of points. The rank formula in the first relevant row is =RANK (B2,$b$2:$b$11,1). This works nicely unless there is a TIE in a score. If there are 4 participants and a tie for second, the resulting points(rankings) would be 4, 2, 2, 1. This is not what I want. I want to distribute all the points amongst all the tied players, so that each player in this scenario would receive 2.5 points. I hope I am making sense here. There are also two things to keep in mind (1) The scores will not necessarily be sorted, because there are many categories to score (2) Although unlikely, there could be more than two teams tied, so points might need to be divided amongst three or more teams. Thanks for any insight anyone mihgt be able to provide.... Thanks, Don |
=AVERAGE(COUNTIF(B$2:B$11,"<"&B2)+ROW(INDIRECT("1: "&COUNTIF(B$2:B$11,B2)))) Maybe this one (not an ARRAY formula) : =COUNTIF(B$2:B$11,"<"&B2)+(1+COUNTIF(B$2:B$11,B2))/2 Regards, Daniel M. |
| All times are GMT +1. The time now is 01:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com