Assigning points to teams based on place in conference standings

[curtam]

RED CONFERENCE SEASON TOTAL WIN LOSS WIN LOSS 1 2 3 4 5 6 7 1 Richards 2 0 2 0 1 7 6 5 4 3 2 1 2 Argo 1 0 2 0 2 6.5 5.5 4.5 3.5 2.5 1.5 2 Shepard 1 0 2 0 3 6 5 4 3 2 2 Evergreen Park 1 1 1 1 4 5.5 4.5 3.5 2.5 2 Reavis 1 1 1 1 5 5 4 3 6 Eisenhower 0 2 0 2 6 4.5 3.5 6 Oak Lawn 0 2 0 2 7 4 1 2 3 4 5 6 7 BLUE CONFERENCE SEASON TOTAL 1 7 WIN LOSS WIN LOSS 2 13 6 1 Lemont 1 0 1 0 3 18 11 5 1 Tinley Park 1 0 1 0 4 22 15 9 4 1 T.F. South 1 0 1 0 5 25 18 12 7 3 1 Hillcrest 1 0 1 1 6 27 20 14 9 5 2 5 Oak Forest 0 2 0 2 7 28 21 15 10 6 3 1 5 T.F. North 0 1 0 1 5 Bremen 0 1 0 2 <tbody> </tbody>

I am looking for a formula that will give me the correct number of points to each team based on their rank in the conference. Because of ties I need to figure that in as well. For example in the red table there is one team in first with four tied for second and two tied for sixth. The point breakdown is in the table to the right. "Richards" in my example would recieve 7 points as the only team currently in first place, while the four teams sharing second would each receive 4.5 points (adding 6, 5, 4, and 3 and dividing by 4). The two teams sharing sixth would then receive 1.5 points each as they slit the final three points available. Is there a way to link through a formula the rank for each team to the data in the table to the right? Any solutions would be great as I am stuck on the proper way to right the formula. Thanks

<tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

Let me get this straight. Are you trying to break ties using conditions? Ignoring that table to the right, are you saying Richards is ranked 1 because it has 2 wins (as do Argo and Shepard) and also has 2 conference wins? Is that right?

 

[curtam]

Thanks for the reply. I would actually like to have the teams placed by win percentage, then by head to head wins, then by total wins if possible. I just wasn't sure what the proper formula would be to try and do something like this

 

[DRSteele]

Ranking things in a statistics table can be achieved by creating cascading rules by order of magnitude, creating a summary Grade for each team, and ranking according to that Grade. When you include the head-to-head data in the tie-breaking procedure, things become onerous and nearly impossible to do in Excel.

See these posts to get a feel for the issue, especially

https://www.mrexcel.com/forum/excel...t-highest-similar-values.html?highlight=grade

and also

https://www.mrexcel.com/forum/excel...tie-break-criteria.html?highlight=tie+breaker
https://www.mrexcel.com/forum/excel...sing-3-columns-data.html?highlight=rank+grade