Help needed w/ comprehensive spreadsheet (nerd alert)

[slatter]

Brains of TheRX, I come to you in need of a geeky math formula. After toying around with this for a while, I am not convinced that a formula even exists to get me in the right ballpark for what I'm trying to do. That said, here's what I'm working on.

I've created a spreadsheet of the 120 NCAA FBS teams. Next to every team is their:

• strength of schedule (ranked 1-120 by Phil Steele)

• their ranking 1-120 (by Phil Steele, The Sporting News, and a respected local ACC beat writer)

• Phil Steele's home field advantage (a number typically between 2 and 5 for how many points the home crowd is worth)

• number of home games

• record Straight Up in 2008

• record ATS in 08 and 07

• record Over/Under in 08 and 07

• returning offensive starters

• returning defensive starters

• whether or not the team has a new head coach

• whether they have a new offensive and/or defensive coordinator

A lot to digest, I know.

Anyway, I'm trying to create a formula that properly weighs these categories. Of course I'm open for suggestions for other categories to add into the equation. Right now here's what I have, and it's not right. It just isn't.

Spot in the power 120 = weighted times 10
Strength of schedule = weighted times 1
Home field advantage = weighted times 5
Returning offensive starters = weighted times 2.5
Returning defensive starters = weighted times 2.5
New head coach = whole formula multiplied by 2.

I also realize that the flaw in looking at this from a week-to-week perspective is that it includes strength of schedule (which does not necessarily matter on a week to week basis) as well as their home field advantage (doesn't help me much when the team is on the road).

Sorry, that's a ton of information, and it may be hard to visualize. I don't want to just post the whole thing, but if anyone needs a visualization of this, I am happy to PM you this chart. Hopefully you'll actually help be productive and help me add a category or two, or at least help me out with the formula, and not just take the spreadsheet for your own entertainment (though I don't care if you use it for reference throughout the season). Thanks in advance!

 

[ripper]

For many years I was using a program called collegio college football,..that had weekly updates on each teams statistics. It had a matchup function that had alot of what you describe already done for you.. For some reason, the program never was rolled out last season. I've written to the publisher to see if this year was a go and have not received a response as of yet. Most of all what your looking for was included, as well as multiple news links for each team.

I would like to know from the other readers if there are any other programs availble that may be comparable?.

 

[slatter]

Bump. :>(

 

[bellyputter]

I will trade all the spreadsheets in the world for any pertinent information that hasn't been released.

 

[russ1945]

A spreadsheet is really just meant to make it convenient to compare points of interest. There are a lot of factors, one I noticed you did not consider is strength of schedule. Putting all of these comparisons into a formula is a whole different thing and it all has to do with relevance. I did a spreadsheet too but more or less so I could do an overlay for the teams and compare some things I wanted to compare ina one on one matchup. Basically what you are talking about is a power rating of some sort.

 

[bellyputter]

I will trade all the spreadsheets in the world for any pertinent information that hasn't been released.

Unless it's Mr. Walters' spreadsheet!

 

[russ1945]

I will trade all the spreadsheets in the world for any pertinent information that hasn't been released.

Would you release it if you were the only one who knew it?

 

[slatter]

A spreadsheet is really just meant to make it convenient to compare points of interest. There are a lot of factors, one I noticed you did not consider is strength of schedule. Putting all of these comparisons into a formula is a whole different thing and it all has to do with relevance. I did a spreadsheet too but more or less so I could do an overlay for the teams and compare some things I wanted to compare ina one on one matchup. Basically what you are talking about is a power rating of some sort.

I did use Phil Steele's strength of schedule.

 

[russ1945]

I did use Phil Steele's strength of schedule.

Well it all breaks down the same. Just because you have a spreadsheet does not mean that you will be able to use everything on that spreadsheet to come up with a power rating (which I guess is what you are looking for). Strength of schedule is one of many and you are relying on one source (there are more). You will not be able to come up with a power rating up front because you have to verify and adjust the numbers that you are using. It may take a couple of years to tweak it. You will also need to add or subract factors to get where you are going. In short, there is no magic formula and no shortcuts. I think that the main benefit of doing what you are doing is that making that spreadsheet has familiarized you with info and stats first hand and that the knowledge you gain from compiling that spreadsheet will reap its own rewards.

 

[DollarDay]

Slatter,

Besides Phil Steele's info I would try to build in a lot more cappers formulas, etc. One easy one to get and use is Jeff Sagarin's data. It's published at the USAToday website and it's free. something like

http://www.usatoday.com/sports/sagarin.htm

He's got the 2008 college football "final" sheet up now that you can get.

I'm certainly not much help on weights but this should lead you in the right direction.

 

[slatter]

Thanks baccy, russ, and everyone else for your input. I know there's no magical formula that will do all my work for me, but I am trying to get close. I will definitely check out the Sagarin ratings baccy. Thanks guys!

 

[letsgohoosiers]

I'm not sure I understand what's wrong.

Are you simply doing something like this?

=AVERAGE((A1*10),(B1),(C1*5),(D1*2.5),(F1*2.5),(G1*2))

AVERAGE was just something I thought of, you can also use SUM.

 

[slatter]

More or less. There's also a part to the formula that weighs it heavier for a team with a new head coach.