Lineup Optimization

I just finished reading a great article over at Beyond the Boxscore about the differences between how OBP and SLG should be valued based on position in the lineup.

I tried to run the numbers for the A's projected lineup. I used PECOTA projections from Baseball Prospectus. It was fairly easy to get the numbers to work for me, to a point. While I could create a lineup arbitrarily and see how it would project out, I could not efficiently run an optimization. It's a fairly straightforward thing to ask of my computer, but there are limits to spreadsheets, as far as I know how to utilize them. Here's the data I have in there:



The numbers to the left of the names are the coefficents for how much to weight a single point of OBP or SLG. For instance, one point of OBP is three times as important as one point of SLG for the leadoff man.

The grid to the right of the players is the coeffiecient multiplied by their OBP or SLG.

All of this data can be used like this:

Expected Runs per Game = obpC1(OBP1) + obpC2(OBP2) + ... obpC9(OBP9) + slgC1(SLG1) + ... slgC9(SLG9) + CONSTANT

That means that you simply take the coefficient times OBP or SLG of a player in a certain lineup spot, and add them all up.

Example:

Mark Ellis batting leadoff, through Jason Kendall in the #9 spot (as I have it listed above)

Expected Runs per Game = 2.997(.351) + 2.255(.355) + ... 2.550(.333) + .931(.426) + ... .539(.338) + -5.261


At least I think that's what's happening there.

What I need is to be able to run a program where the lineups would be randomly generated and the computer would return a lineup that would be optimized to have the highest Expected Runs per Game. It sounds easy, but I have no idea how to do that in a spreadsheet, and have serious doubts it's even possible. I'd imagine I could do that with R, a stats program, but R is a bit complex and academic.

A simulator may also work, like Diamond Mind, but that's too expensive for me to get into.

SABR has done some work on this kind of thing, perhaps I should take advantage of my SABR membership and contact the hive mind over there...

Here are a couple links on lineup construction:

Cyril Morong, the author of the Beyond the Boxscore article, has delved into this subject before.

Keith Woolner at Baseball Prospectus looks at a balanced lineup's possible advantages.