Ranking by Category

[razzmatazz]

I have a s/s that ranks a team by position and assigns points overall. What I want to do is assign points to a team based on a category field i'e womans , mens and veterans. What happens now is that if there are 5 teams they score 5 points for 1st, 4 points for 2nd etc. what I want to do is if there are say 5 teams and 2 are womens teams and 3 are mens I want to assign points based on that category - so if you are ist in mens team you get 3 points for 1st, 2 points for 2nd etc, if you are 1st in womens category you get 2 points for 1st and 1 for second. How do I rank and divide by the number of teams in a certain category without having to seperate the data into different columns - I still want to rank and assign points with all teams as well

 

[jimrward]

can you post some data please from your current layout

 

[razzmatazz]

First sheet - team names and catergory(division)
<TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 8886" width=243><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: black" width=51 height=21>Team #</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=243>Name</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=61>Division</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc">Micky</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">mens</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc">Donald</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">mens</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc">Minnie</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">womens</TD></TR></TBODY></TABLE>

event
<TABLE style="WIDTH: 430pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=573 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 8886" width=243><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" span=3 width=93><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: black" width=51 height=21>Team #</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=243>Team Name</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=93>Time</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=93>Position</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=93>Event Points</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mickey</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: red">03:42.36</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">3</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Donald</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: red">03:07.45</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Minnie</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: red">03:13.65</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2</TD></TR></TBODY></TABLE>

The formula I use to assign points
=IF(ISERROR(RANK(C3,C$3:C$5,1)+(COUNTIF(C$3:C$5,C3)-1)/2),"",(RANK(C3,C$3:C$5,1)+(COUNTIF(C$3:C$5,C3)-1)/2))

I just want to add a category rank so that the points would be assigned in relation to what catergory they are in i.e mens or womens. Donald would end up with 2 points Mickey 1 and Minnie 1(only because there are no other womens teams in this example)

 

[jimrward]

ok try this for the points, it is essentially a ranking by group but turned on its head so the the best score gets the lowest ranking which will give you the points required

=SUMPRODUCT((C2=$C$2:$C$6)*(D2>$D$2:$D$6))+1

in column C put the Division data men, women etc,

in column D put your times or scores

my formula runs from Row2 to Row6, change 2 and 6 accordingly for your data range