2014 Women's Hockey RPI Predictor

[TonyTheTiger20]

So, as I mentioned in the Pairwise thread, I thought I would try to put together an RPI predictor, similar to what they have on the men's site around this time of year. I just spent HOURS on this -- literally, probably about 4-5 hours -- and I seriously hope it is working properly and that you all are able to replicate the results. I've had some issues with Google Docs screwing up my formulas in the past.

The predictor is accessable via an Excel spreadsheet that will allow you to enter results for the games this weekend, and it will spit out the resulting RPI. You can access it in one of two places:

Here: http://tinyurl.com/online2014wihrpi This is the spreadsheet converted to Google Docs format. You should be able to edit the values online and wouldn't have to download the sheet to your computer. Just a caveat that if more than one of you are playing with the values at the same time things will probably get screwy.

Or, here: http://tinyurl.com/2014womensrpi Which will allow you to download the sheet to your computer in xls format if you like.

No, I'm not giving you a virus. I promise. But if there are some mysterious charges on your credit cards next week consider it fair payment for all the work I put into this.

Instructions:

You are only able to edit the fields in red on the 'predictor' sheet. They are all dropdown menus. Select your choice for the winner of each game and the "RPI Rankings" in green right next to it will automatically update with the resulting final RPI.

Notes:

--This does not take into account Common Opponents nor TUC. I built this as a way to see what would happen specifically to the RPI with the results of each game. So beware that a team could be losing TUC and CoOpp points to a nearby team and might actually be below them in the Pairwise.

--This does not take into account the small RPI fluctuations that occur due to results of other games. For example, If BC beat Northeastern, it would slightly decrease Wisconsin's RPI because Wisconsin played the Huskies. The spreadsheet does not take these minor fluctuations into account, however the effect from this should be minimal.

If I screwed up on something or it just isn't working, please let me know.

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

There seems to be an issue with the green cells *sometimes* not re-sorting properly after you make a change. If you make your changes, save the doc, and then reopen, it should correct itself.

 

[Eeyore]

Re: 2014 Women's Hockey RPI Predictor

At least in my version of Excel, the VLOOKUPs in Column H don't like a numeric value for the TRUE and FALSE answers in Columns E and G. So when you change resluts the only changes you get in RPI are for the winner of that tournament based upon the change in opponent. The logical tests for Columns E and G were always coming up False and so the RPI calculation you got was winning the semi and losing the final for every team.

Changing the VLOOKUP formulas to testing for Columns E and G as either TRUE or FALSE rather than 1 or 0 seems to have solved everything. As I said, it may work different in Excel for others.

 

[Eeyore]

Re: 2014 Women's Hockey RPI Predictor

Having played with it, it doesn't look like BC can pass Wisconsin in RPI even if North Dakota wins tomorrow. And since BC can't flip the Common Opponents criterion and the gap in record vs. TUC isn't that large, I think the Badgers are safely on the other side of the bracket from Minnesota.

 

[Numbers]

Re: 2014 Women's Hockey RPI Predictor

Eeyore,

I think I made the same changes in the spreadsheet as you, Eeyore.

Try this:
Minnesota wins WCHA over UND
BC wins HEA over BU
Cornell wins ECAC over Clarkson
Mercyhurst wins CHA over RMU

Now, I get:
Minn .6735
Cornell .6032
Clarkson .5995
Wiscon .5990
BC .5967
Harv .5843

This is as close as I can get, and yet, even here, Wisconsin wins the compare with Clarkson on TUCs and ComOpp

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

At least in my version of Excel, the VLOOKUPs in Column H don't like a numeric value for the TRUE and FALSE answers in Columns E and G. So when you change resluts the only changes you get in RPI are for the winner of that tournament based upon the change in opponent. The logical tests for Columns E and G were always coming up False and so the RPI calculation you got was winning the semi and losing the final for every team.

Changing the VLOOKUP formulas to testing for Columns E and G as either TRUE or FALSE rather than 1 or 0 seems to have solved everything. As I said, it may work different in Excel for others.

I think I made the same changes in the spreadsheet as you, Eeyore.

You guys are the freaking BEST, thank you!! It was impossible to tell what was wrong on my version because it was working fine but NUWHDogHouse was having issues on his and I couldn't figure out why.

I made the change to my original sheet and updated the URL in the original post. Can someone let me know if the issue is fixed now when you get a chance?

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

I edited the original post to include a link to the online version which should make it a lot easier to play with for people who don't want to or can't or just find it plain inconvenient to download it to their computer. Don't mess anything up!

 

[OnMAA]

Re: 2014 Women's Hockey RPI Predictor

Grant for Commisioner. This is great stuff. Do you guys have a job ?

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

Do you guys have a job ?

Yes though not one that involves spreadsheets... I'm working on changing that haha

I'm not able to find a scenario that has BC 4th and Harvard 5th. Anyone else? I guess intuitively that makes sense since I don't off the top of my head think Harvard can't get out of 6th without winning the ECAC, and in that case they would flip with BC.

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

Copied from the Wisconsin thread because it's an interesting little case study (and proof that my numbers aren't going to be perfect) but I'm happy so far with how close the one result that has updated is to my sheet.

Grant, have you checked your predictor against the Mercyhurst result? You have them 0030 too high compared to USCHO's RPI. I think you said (understandably) that you didn't try to do the secondary and tertiary 'effects'. Maybe because Syracuse is a Mercyhurst opponent that just lost a game accounts for the 0030?

The predictor doesn't work with an incomplete 'bracket' -- as in, you can't just delete the finals results and have it spit out resulting RPIs. I'm guessing that's what you're trying to do.

Looking at the second sheet in the workbook ("RPI") though, and if you go to the RPI W1 and RPI L1 columns, you'll have each respective teams' RPIs depending on the result of their first game (RPI W1 being the RPI if they won, of course, and RPI L1 being if they lost). Looking at Mercyhurst on USCHO versus my sheet, it looks like I have Hurst as undervalued by .001 and Syracuse as overvalued by 0.0002.

One thing with my RPI is it can't remove the results against bad opponents so I had to put in an adjustment (Column H on the 'RPI' sheet is how much). That adjustment is how far off the USCHO RPI (which DOES remove 'bad wins') was from my calculated RPI. I just added that adjustment in to every result in the table. It's not perfect but I figured it was better than nothing.

That could partially explain why Syracuse's expected vs. actual result is closer than Mercyhurst's, because they didn't have a 'bad win' adjustment to further muddy the results.

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

Here are the differences from the sheet to the actual results after day 1. They're all pretty close, although Minnesota & Minnesota Duluth are further off than the rest. I have a feeling it has to do with my crude 'adjustment' (the last column) to try to take into account the RPI adjustment for bad wins since Minnesota's is so much bigger than everyone else's.

If I can find a way to pull the data from USCHO's team schedule pages I should be able to create one with no errors next year. It would just be a more in depth sheet and I didn't really have the time to put that together in one night.

[i][b]After 3/7	   Sheet   Actual  Difference  Adjustment?[/b][/i]
Minnesota	   0.6743  0.6760 -0.0017      0.0228
Wisconsin	   0.5990  0.5984  0.0006      0.0017
North Dakota	   0.5566  0.5560  0.0006      0
Minnesota Duluth   0.5179  0.5160  0.0019      0
Mercyhurst         0.5656  0.5664 -0.0008      0.0005
Syracuse	   0.5144  0.5143  0.0001      0
Robert Morris	   0.5575  0.5572  0.0003      0.0006
RIT	           0.4984  0.4987 -0.0003      0

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

So I finished my 'exact' RPI calculator! A bit late to be used as a predictor, but you can use it to run some 'what if?' questions. For example, had BC beaten BU, Wisconsin would indeed have been travelling to Minnesota.

Here is the link:

http://tinyurl.com/womens-rpi-2014

No online version of this one because it's huge and google docs doesn't support the VLOOKUP formula. But it's easy to use.

Just download the file, and go to the 'composite' tab. Edit the score of any game to change the result of that game in the final RPI calculation. To add a game or change an opponent (for example, if you want to see NU win the Hockey East title) just change the name of the team in each respective field. You should *only* do this on the "Composite" tab.

You can also add or delete games. Just delete the teams and scores or add teams and scores to any line. It is VERY IMPORTANT that you type the names of the team exactly.

Anyway, this will be usable for the entire 2014-2015 season as well. Next project is to have it show the full pairwise rather than just RPI!

 

[wwhyte]

Re: 2014 Women's Hockey RPI Predictor

Great job, Grant!

 

[OnMAA]

Re: 2014 Women's Hockey RPI Predictor

So I finished my 'exact' RPI calculator! A bit late to be used as a predictor, but you can use it to run some 'what if?' questions. For example, had BC beaten BU, Wisconsin would indeed have been travelling to Minnesota.

Here is the link:

http://tinyurl.com/womens-rpi-2014

No online version of this one because it's huge and google docs doesn't support the VLOOKUP formula. But it's easy to use.

Just download the file, and go to the 'composite' tab. Edit the score of any game to change the result of that game in the final RPI calculation. To add a game or change an opponent (for example, if you want to see NU win the Hockey East title) just change the name of the team in each respective field. You should *only* do this on the "Composite" tab.

You can also add or delete games. Just delete the teams and scores or add teams and scores to any line. It is VERY IMPORTANT that you type the names of the team exactly.

Anyway, this will be usable for the entire 2014-2015 season as well. Next project is to have it show the full pairwise rather than just RPI!

Reverse engineering at it's finest.

 

[OnMAA]

Re: 2014 Women's Hockey RPI Predictor

So I finished my 'exact' RPI calculator!

Nicely done. I know you were frustrated the other day with that "RPI adjustment" for wins vs weak opponents. Did you finally figure out that adjustment factor trick, and if so what was the secret. ?

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

Nicely done. I know you were frustrated the other day with that "RPI adjustment" for wins vs weak opponents. Did you finally figure out that adjustment factor trick, and if so what was the secret. ?

I did -- the 'secret' (or, more accurately, 'how I was screwing up') is that the adjustment is iterative. That is, you compare all of the wins to the RPI, remove the ones that lower the RPI, and adjust the RPI upward. THEN, you compare all of the wins to the *ADJUSTED* RPI, remove the ones that lower the adjusted RPI, and adjust the RPI upward *again*.

You do that until the RPI does not increase any more after successive iterations -- in practice, two iterations has been enough every time (even on Minnesota's massive RPI adjustment), but I have the sheet doing it thrice just in case.

 

[Eeyore]

Re: 2014 Women's Hockey RPI Predictor

You do that until the RPI does not increase any more after successive iterations -- in practice, two iterations has been enough every time (even on Minnesota's massive RPI adjustment), but I have the sheet doing it thrice just in case.

I enjoyed the fact that last year, the Gophers' RPI calculation dropped every opponent except North Dakota.

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

I enjoyed the fact that last year, the Gophers' RPI calculation dropped every opponent except North Dakota.

Ahahaha yes -- although I guess if you're undefeated then EVERY opponent will hurt your RPI except for the highest ranked one.

 

[Eeyore]

Re: 2014 Women's Hockey RPI Predictor

Ahahaha yes -- although I guess if you're undefeated then EVERY opponent will hurt your RPI except for the highest ranked one.

Exactly.

 

[TonyTheTiger20]

Re: 2014 Women's Hockey RPI Predictor

I have converted the RPI calculator into a PWR calculator. I...... can't even tell you how much work this was.

You can download it here:

http://tinyurl.com/wih2014pwr

It is an extraordinarily large excel spreadsheet. Same deal as before, you edit the results in the "Composite" sheet and all other results (specifically the RPI and PWR sheets) will update accordingly.

Let me know if you find any errors. I will update the sheet for 2014-2015 when the season rolls around.

WOOOOOOOOOOHOOOOOOOOOOOOOOOOOOOOOO! Thanks for the encouragement, everyone.

http://static.fjcdn.com/gifs/We+did...lbert+is+50+thousand+times_eda923_3860544.gif