What's new
By:
USCHO Fan Forum

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • The USCHO Fan Forum has migrated to a new plaform, xenForo. Most of the function of the forum should work in familiar ways. Please note that you can switch between light and dark modes by clicking on the gear icon in the upper right of the main menu bar. We are hoping that this new platform will prove to be faster and more reliable. Please feel free to explore its features.

computer issues: part infinity

Re: computer issues: part infinity

Because I'm a nerd, I'm trying to apply the college hockey pairwise rankings to other sports using Excel. Initially I was going to do it for all of college football, but after realizing that's a lot of work, I'm starting with just the Big Ten as a test for formulas and whatnot. My set up is probably not ideal, but this is what I've come up with so far. I understand it probably doesn't work due to the limited number of games, but I'm curious and like to play around with Excel from time to time.

[table="width: 500, class: grid, align: center"]
[tr] Indiana
[td]Indiana[/td]
[td]W/L[/td]
[td]Maryland[/td]
[td]W/L[/td]
[/tr]
[tr]
[td]Indiana[/td]
[td][/td]
[td]Indiana[/td]
[td]W[/td]
[/tr]
[tr]
[td]Maryland[/td]
[td]L[/td]
[td]Maryland[/td]
[td][/td]
[/tr]
[tr]
[td]Michigan[/td]
[td]L[/td]
[td]Michigan[/td]
[td]W[/td]
[/tr]
[tr]
[td]Michigan St[/td]
[td]L[/td]
[td]Michigan St[/td]
[td]L[/td]
[/tr]
[tr]
[td]Ohio St[/td]
[td]L[/td]
[td]Ohio St[/td]
[td]L[/td]
[/tr]
[tr]
[td]Penn St[/td]
[td]L[/td]
[td]Penn St[/td]
[td]W[/td]
[/tr]
[tr]
[td]Rutgers[/td]
[td]L[/td]
[td]Rutgers[/td]
[td]W[/td]
[/tr]
[tr]
[td]Illinois[/td]
[td][/td]
[td]Illinois[/td]
[td][/td]
[/tr]
[tr]
[td]Iowa[/td]
[td]L[/td]
[td]Iowa[/td]
[td]W[/td]
[/tr]
[tr]
[td]Minnesota[/td]
[td][/td]
[td]Minnesota[/td]
[td][/td]
[/tr]
[tr]
[td]Nebraska[/td]
[td][/td]
[td]Nebraska[/td]
[td][/td]
[/tr]
[tr]
[td]Northwestern[/td]
[td][/td]
[td]Norhtwestern[/td]
[td][/td]
[/tr]
[tr]
[td]Purdue[/td]
[td]W[/td]
[td]Purdue[/td]
[td][/td]
[/tr]
[tr]
[td]Wisconsin[/td]
[td][/td]
[td]Wisconsin[/td]
[td]L[/td]
[/tr]
[/table]

It's worked for me so far with head to head, but I'm running into a problem with common opponent. Since football (95% of the time) only plays each team once per year, I don't really see the need to set up a winning percentage equation, instead just giving a point per win of each common opponent to represent the 1.000 winning percentage against each opponent. (i.e. above, Indiana has 0, Maryland has 4 for their four 1.000 percentages against common opponents with Indiana).

If possible, I would like to be able to set up an If Statement that basically says "If A1 thru A13 and B1 thru B13 share a row with a W or L, count 1 per W."

So far, I've come up empty in my lame attempts and beginning to think that even if such an equation is possible, it would be impossible to simply drag it to recreate for other teams, and would have to retype it for every team. Though that's probably faster than manually counting, especially if I want to try to expand this into other conferences.
 
Re: computer issues: part infinity

I have a PWR spreadsheet I did for college hockey a few years back. It's my masterpiece spreadsheet. Took a couple of rainy weekends to complete but it works.

Some of the equations are batsheet crazy. It has something like 17 MB of formulas for the few KB of data. It also takes 15-30 seconds to recalculate depending on the computer and data changes.

Here's one of the formulas for TUC back when they had that:
=IFERROR(IF(D$60-D10+D$121-D71+D$182-D132>9,IF(INDEX($C$1:$BJ$60,MATCH("SUM",$C$1:$C$60,0),MATCH($C194,$C$1:$BJ$1,0))-INDEX($C$1:$BJ$60,MATCH(D$185,$C$1:$C$60,0),MATCH($C194,$C$1:$BJ$1,0))+INDEX($C$62:$BJ$121,MATCH("SUM",$C$62:$C$121,0),MATCH($C194,$C$62:$BJ$62,0))-INDEX($C$62:$BJ$121,MATCH(D$185,$C$62:$C$121,0),MATCH($C194,$C$62:$BJ$62,0))+INDEX($C$123:$BJ$182,MATCH("SUM",$C$123:$C$182,0),MATCH($C194,$C$123:$BJ$123,0))-INDEX($C$123:$BJ$182,MATCH(D$185,$C$123:$C$182,0),MATCH($C194,$C$123:$BJ$123,0))>9,IFERROR((SUM(D$2: D$59)+0.5*SUM(D$124: D$181)-HLOOKUP(D$185,'PWR Comp'!$A$184:$BH$243,MATCH($C194,'PWR Comp'!$A$184:$A$243,0),FALSE)-0.5*HLOOKUP(D$185,RPI!$A$413:$BH$473,MATCH($C194,RPI!$A$413:$A$473,0),FALSE))/(SUM(D$2: D$59)+SUM(D$63: D$120)+SUM(D$124: D$181)-HLOOKUP(D$185,'PWR Comp'!$A$184:$BH$243,MATCH($C194,'PWR Comp'!$A$184:$A$243,0),FALSE)-HLOOKUP(D$185,RPI!$A$413:$BH$473,MATCH($C194,RPI!$A$413:$A$473,0),FALSE)-HLOOKUP(D$185,RPI!$A$348:$BH$408,MATCH($C194,RPI!$A$348:$A$408,0),FALSE)),""),0),0),"")

Common opponents tab was 15,000 rows long
 
Last edited:
Re: computer issues: part infinity

Also note that RPI is more complex than just winning percentages. When calculating it, each opponent has to be removed when you figure out overall winning percentages. Unless they've changed that. So when you're trying to figure out Team A's opponents' winning percentage, you have to remove all games that Teams B-Z played against Team A.

For example:
=IF(B180="","",IFERROR((HLOOKUP(B124,$A$283:$BH$343,MATCH("SUM",$A$283:$A$343,0),FALSE)-HLOOKUP(B124,$A$283:$BH$343,MATCH(B$479,$A$283:$A$343,0),FALSE)+0.5*(HLOOKUP(B124,$A$413:$BH$473,MATCH("SUM",$A$413:$A$473,0),FALSE)-HLOOKUP(B124,$A$413:$BH$473,MATCH(B$479,$A$413:$A$473,0),FALSE)))/(HLOOKUP(B124,$A$283:$BH$343,MATCH("SUM",$A$283:$A$343,0),FALSE)-HLOOKUP(B124,$A$283:$BH$343,MATCH(B$479,$A$283:$A$343,0),FALSE)+HLOOKUP(B124,$A$348:$BH$408,MATCH("SUM",$A$348:$A$408,0),FALSE)-HLOOKUP(B124,$A$348:$BH$408,MATCH(B$479,$A$348:$A$408,0),FALSE)+HLOOKUP(B124,$A$413:$BH$473,MATCH("SUM",$A$413:$A$473,0),FALSE)-HLOOKUP(B124,$A$413:$BH$473,MATCH(B$479,$A$413:$A$473,0),FALSE)),""))
 
Re: computer issues: part infinity

Want a copy?

I also have a KRACH calculator that was similarly difficult to put together. It's even more of a resource hog since it's an iterative calculation.
 
Last edited:
Re: computer issues: part infinity

I was just stealing RPI from another guy for football. But, I don't know if I have the commitment in me to expand further than the Big Ten if it's going to be that huge. Hockey has more games but football has more than double the teams. At least this way if I can't get a formula I can still manually count common opponent, despite that being a chore.
 
Re: computer issues: part infinity

Yeah, with 59 teams you'd have nearly double the rows in that TUC spreadsheet. But since TUC isn't a category with the new system, I need to rebuild the entire spreadsheet.

This is really something that is better suited for a database. Excel has made huge strides in the relational database field, but this pushes Excel to its limits.
 
Re: computer issues: part infinity

Oh, and since there is somewhat significant crossover, do you want to include D1-AA teams?
 
Re: computer issues: part infinity

At least at D-III the calculation of OWP and OOWP is the average of your opponents' win pcts, not the summation of their records.
 
Re: computer issues: part infinity

That was another issue I was running into. Does hockey count games against D2 and D3 schools as regular season? I know the basketball committee looks at games against D1 only for the tournament. Preferably I would like to have it FBS only, because while I love the App State and NDSU stories, I hate when FBS plays FCS.
 
Re: computer issues: part infinity

dxmnkd316 said:
Yeah, with 59 teams you'd have nearly double the rows in that TUC spreadsheet. But since TUC isn't a category with the new system, I need to rebuild the entire spreadsheet.

This is really something that is better suited for a database. Excel has made huge strides in the relational database field, but this pushes Excel to its limits.
Click to expand...

Yeah, I'm thinking that if this was done using SQL Server or the like it would be an order of magnitude faster and require an order of magnitude less code.
 
Re: computer issues: part infinity

Spartanforlife4 said:
That was another issue I was running into. Does hockey count games against D2 and D3 schools as regular season? I know the basketball committee looks at games against D1 only for the tournament. Preferably I would like to have it FBS only, because while I love the App State and NDSU stories, I hate when FBS plays FCS.
Click to expand...

No. Only D1 to D1.

I'm currently adapting my sheet to see if it can even handle the 128 teams for D1-A.
 
Re: computer issues: part infinity

dxmnkd316 said:
No. Only D1 to D1.

I'm currently adapting my sheet to see if it can even handle the 128 teams for D1-A.
Click to expand...

Well, if it can't, I would still like a copy to see if my template is even correct in what I'm doing, because I think it would be cool to do this for each conference if I can't do it all together. My email should be in your rep.
 
Re: computer issues: part infinity

It's painful. I almost need to rebuild it from scratch because each time I I insert a row it takes about a minute to repopulate the formulas. Even with autocalculate off.
 
Spartanforlife4 said:
That was another issue I was running into. Does hockey count games against D2 and D3 schools as regular season? I know the basketball committee looks at games against D1 only for the tournament. Preferably I would like to have it FBS only, because while I love the App State and NDSU stories, I hate when FBS plays FCS.
Click to expand...
For hockey, games outside your division do not count towards selection or stats.
 
Re: computer issues: part infinity

Well, after a busy week I finally went back and finished it. It's a complete home made bike, and I didn't even get a formula for common opponents, so that'll be the next step, but it's finished. Here are the final standings compared to what would be the Big Ten final standings if it was one division. I didn't plug in any tiebreakers for the one division standings because it was for quick comparison reasons, so there may be one or two moves that I missed.

Pairwise:
Ohio St-13
Michigan St-12
Wisconsin-11
Nebraska-9
Minnesota-8
Maryland-5
Rutgers-5
Iowa-5
Illinois-5
Michigan-3
Northwestern-2
Penn St-1
Indiana-1
Purdue-0

Traditional:
Ohio St
Michigan St
Wisconsin
Nebraska
Minnesota
Maryland
Iowa
Illinois
Michigan
Northwestern
Rutgers
Penn St
Indiana
Purdue

The only mover, and a big mover at that, is Rutgers, who jumps up four spots thanks to their high RPI which puts them above the other two teams they're tied with in PWC. However, this is a flaw in the model because the RPI I took was using full season results, and my pairwise only includes conference play and the championship game. Still, I'm happy that I was able to find a conclusion out of this, and if I can find a way to get that common opponent equation to work, as well as finding a site that warehouses schedules and RPI in a way that can be easily imported to Excel, I would be willing to give it a shot for all of FBS.

The biggest issue in all of that may actually be finding the data warehoused in a convenient way, because each site seems to sort teams differently, i.e. UC-Berkely vs Cal, CSU-Fresno vs Fresno St, Michigan vs University of Michigan.
 
Re: computer issues: part infinity

Think I finally had a breakthrough on common opponents. Unfortunately, I believe it will mean expanding the table from it's current format of 16,512x4 to 16512x132 and filling in those extra 128x16512 with code. I may have to read up on macros and see if it's possible to do one for this.
 
You must log in or register to reply here.
Back
Top