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.

College Football 2015-16: Bowls, Playoffs, and Offseason

Re: College Football 2015-16: Bowls, Playoffs, and Offseason

And Army could not get out of it's way, as they give up chances to score. Other than one play, the Navy offense did nothing in the second half. 14 years in a row for Navy.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

Spartan4Life:

Question for you on your PWR spreadsheet. Since I'm reupping my PWR spreadsheet for ASU and I'm trying to redo it to make it easier to add in the future. How did you accomplish the common opponents comparison? Mine is preposterously complicated and is probably able to be simplified. Rather, I hope it is.

I basically have a 60x60 table for each team's wins, a table for losses, and a table for ties. Then there's a 60x60 table for each team's record against common opponents.

This creates a behemoth of a tab. It's pretty ridiculous.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

dxmnkd316 said:
Spartan4Life:

Question for you on your PWR spreadsheet. Since I'm reupping my PWR spreadsheet for ASU and I'm trying to redo it to make it easier to add in the future. How did you accomplish the common opponents comparison? Mine is preposterously complicated and is probably able to be simplified. Rather, I hope it is.

I basically have a 60x60 table for each team's wins, a table for losses, and a table for ties. Then there's a 60x60 table for each team's record against common opponents.

This creates a behemoth of a tab. It's pretty ridiculous.
Click to expand...

I sort of have the same thing. I tried to think of a way to simplify it more but I just couldn't. Maybe I'll think of a way during the offseason. The way I have it set up is there is a tab that I enter all the results into. Each team is headed by the team name, game 1 and 2 results, and then whatever is needed after that. For example, in head to head, you're just counting wins, so total wins goes in that last category. In Adj WP I actually add four more columns for game 1 location, game 2 location, adjusted wins, adjusted losses, and then the final column calculates it. Then for common opponent win percentage I actually screwed it up and had the h2h stat, but since very few teams have rematches I was able to manually change that this year, will go in and fix this offseason.

So example, is Army.

Air Force - Game 1 - Game 2- USAF (this is a code for easy search but also doubles as the WP column)
Air Force - 0 - 0 - 0
(this lists all 129 teams I have, so I'll just include some they played)
Army - W - 0 - 1
Boise - W- 0- 1
Michigan State - L - 0 - 0

For Army, who split with FCS it looks like
FCS - W - L - .5

Then there is a 130x129 table for each team that then has the winning percentages for common opponent, using an equation that matches up with the corresponding team, i.e. Here is the equation to see if Air Force and Army played FCS, and if they did, what Air Force's WP was against FCS.
=IF(AND(OR($B131="W",$B131="L"),OR($B1171="W",$B1171="L")),$D131,)

So, if there is a win or loss in Air Force's FCS cell, AND there is a win or loss in Army's FCS cell, then take the win percentage AF has in column D.

So for 129 lines for each team it's just 0, 0.5, or 1 depending on the comparisons and whatever. Then line 130 is the sum of those 129. That sum is then placed into another table on another tab that's just 129x129. Then I flip that table so it compares teams against eachother, and then do a "If blank > blank, 1" for the common opponent point.

If i didn't explain it well enough, let me know and I can try to make it easier. I will say, for the equation I posted above, I did that with a macro, because I had to. I think it's around 2 million cells simply for common opponent. Obviously it will be less for you, but still around 216,000, and probably impossible to do manually unless you really like repetitive tasks. If you think it'll work for you, I can PM it to you or something. I had to get help from the Excel forum, because apparently there are people out there who just enjoy making random excel macros for people. Obviously you'd have more columns since some teams can possibly meet up to 7 or 8 times a year, but I think it would work as long as you add in the tie variable to the equations.

For the other equations, I manually inputted, which I will not be doing again because that takes way too much time. I'm going to try to teach myself some advanced VBA so I can just create macros that insert the equations, like matching the sum total line to the next tab that compares them, because with UAB coming back and Coastal Carolina getting a team in the next couple years I'm going to have to add in some extra columns across many tabs as well as about 260 new lines of games, and then redo the source cell for many of the results. Also, if I am able to find a way to rip scores and turn them into wins and losses easily, I want to try to do a pairwise for basketball, but right now it would be nearly impossible to do manually. With around 360 teams it's something like 130,000 lines just for the schedule, times the cells for up to 4 games a year against a team is just enormous.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

Yeah, I hear your pain. I have a half-azzed FBS one I did a few years back but mothballed. I had aspirations of expanding that to basketball as well. But I think basketball requires a legit database. The size of the spreadsheet would be almost unusable.

Thanks. I'll let you know if I find a way.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

My other problem is that I try to get too flexible with my formulas and tables. I'm thinking that if I actually used named tables it would immensely simplify things.

Right now I'm up around 20 MB and almost all of that is formulas.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

I'm at 25mb, and I'm pretty sure the bulk of that is from the common opponents.

The big thing I would suggest is a common table you can use for each equation like I have. Then you're only having to input a result once, rather than what sounds like multiple times.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

Yeah, I need to take a few days to really rekajigger this thing.

So you have a single table of results rather than giving each team a column with a schedule?
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

It's sort of a combination. I probably didn't explain it clearly enough in the other post. I give each team a headline line, with column titles for their team name, game 1 result, game 2 result, game 1 location, game 2 location. Then I have a list of every team under them, including that team. So Air Force has Air Force through FCS (put them at the end) as possible opponents. Everyone has that same list of possible opponents. It makes it easier as a template for year to year and when it comes to common opponents later.
Then as the season goes I fill in results with W and L and location with H, N, A. Then that master list can be mirrored into other tabs depending on what you need. Like I only need location for one thing so all but one just have the results.

For you, that list is gonna be wider for sure, because theoretically some teams could meet 8 times a season, but after you have that master results list you can copy what you need into other tabs and build the adjusted win percentage or common opponents off of that so everything is somewhat standardized.

Question: do you also use a metric f$&! ton of vlookup?
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

Spartanforlife4 said:
It's sort of a combination. I probably didn't explain it clearly enough in the other post. I give each team a headline line, with column titles for their team name, game 1 result, game 2 result, game 1 location, game 2 location. Then I have a list of every team under them, including that team. So Air Force has Air Force through FCS (put them at the end) as possible opponents. Everyone has that same list of possible opponents. It makes it easier as a template for year to year and when it comes to common opponents later.
Then as the season goes I fill in results with W and L and location with H, N, A. Then that master list can be mirrored into other tabs depending on what you need. Like I only need location for one thing so all but one just have the results.

For you, that list is gonna be wider for sure, because theoretically some teams could meet 8 times a season, but after you have that master results list you can copy what you need into other tabs and build the adjusted win percentage or common opponents off of that so everything is somewhat standardized.

Question: do you also use a metric f$&! ton of vlookup?
Click to expand...

Yes and no. I use a combo of match, index, and lookups. For OW%:
=IF(B214="","",IFERROR((HLOOKUP(B102,$A$325:$BI$386,MATCH("SUM",$A$325:$A$386,0),FALSE)-HLOOKUP(B102,$A$325:$BI$386,MATCH(B$524,$A$325:$A$386,0),FALSE)+0.5*(HLOOKUP(B102,$A$457:$BI$518,MATCH("SUM",$A$457:$A$518,0),FALSE)-HLOOKUP(B102,$A$457:$BI$518,MATCH(B$524,$A$457:$A$518,0),FALSE)))/(HLOOKUP(B102,$A$325:$BI$386,MATCH("SUM",$A$325:$A$386,0),FALSE)-HLOOKUP(B102,$A$325:$BI$386,MATCH(B$524,$A$325:$A$386,0),FALSE)+HLOOKUP(B102,$A$391:$BI$452,MATCH("SUM",$A$391:$A$452,0),FALSE)-HLOOKUP(B102,$A$391:$BI$452,MATCH(B$524,$A$391:$A$452,0),FALSE)+HLOOKUP(B102,$A$457:$BI$518,MATCH("SUM",$A$457:$A$518,0),FALSE)-HLOOKUP(B102,$A$457:$BI$518,MATCH(B$524,$A$457:$A$518,0),FALSE)),""))

It's ugly, but it works.

Though I'm finding my hockey RPI calculation no longer matches the siouxsports, USCHO, or Rennselaer's RPI. Specifically the OOWP... I'm not sure why either. Take Air Force and Denver:
Air Force has Denver's OOWP as .5305. http://siouxsports.com/hockey/rankings/rpidetails.php?teamid=27

However, if you go to Denver's page and you average the OWP, you get .5322. Which is what I get in my spreadsheet. http://siouxsports.com/hockey/rankings/rpidetails.php?teamid=3

Any idea?

Edit: It's worth noting my OWP matches the listed values. Just not the OOWP or the Opp Win% listed at the top of each page.
 
Last edited:
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

Rick Brattin, State Rep in Missouri is sponsoring a bill to stop football players from protesting as they did this season.

I give him a month before he's so toxic he has to step down.
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

JF_Gophers said:
Rick Brattin, State Rep in Missouri is sponsoring a bill to stop football players from protesting as they did this season.

I give him a month before he's so toxic he has to step down.
Click to expand...

what can they really do to the guys that's even close to being legal? or enforceable even??
 
Re: College Football 2015-16: Bowls, Playoffs, and Offseason

This is legislation that effectively seeks to make scholarships an employer-employee relationship subject to at-will termination by the university.

The language of the bill provides that any college athlete on scholarship who refuses to play for a reason unrelated to health shall have his or her scholarship revoked.
 
You must log in or register to reply here.
Back
Top