Fantasy Baseball Spreadsheet

I run a fantasy baseball league and write up a report for the owners to read every week. It is quite extensive, but for one section, I'm having issues.

I would like to have Excel sort out the optimal lineup. I've got the pitching handled, I just need to figure out the batters. Sheet1 on the attachment shows the results I'm looking to get, Sheet2 is my progress so far.

I would appreciate any help y'all could offer, here's some of the specifics you should know if you decide to help.

1. each lineup has to start a C, 1B, 2B, 3B, SS, 3 OFs, and a DH. The DH can be any batter regardless of position. And a player can only be in a position that he his eligible for (which is denoted in the "Eligible" column).

2. I'd like to do this without Macros or any additional programing, only what a standard excel can do, as I often use this report while at work, and my work doesn't allow the extras for Excel.

3. I need to have something that can work for each team on each week. I plan on having a formula in each column row 3 that I can fill down. Each team's stats for the next week will be added under the previous week in columns A-F.

5. The biggest problem I've run across is the multiple position eligibility. You'll be able to see that I've got it sorted out up until that point.

6. I don't mind using all available columns in Excel and I don't mind how big the file gets. I am a big fan of helper rows and checker rows.

I'm open to many options. You'll be able to see I don't mind redundancy.

Thanks for any help,
jed

er,baseball? wots that. don't understand even the basic

C, 1B, 2B, 3B, SS, 3 OFs, and a DH

perhaps an example of what you require /where in an excel workbook would be a good start

baseball is a sport in america. it is vaguely similar to cricket. I won't get in to the differences because this isn't the place. Fantasy baseball is a game that you can play that is based on the statistics from real Major League Baseball players.

C, 1B, 2B, 3B, SS, 3 OFs, and a DH denotes the position the fielders play. I added that only to denote the numbers needed, you can see in the "eligible" column which position each player is allowed to play.

I'm not certain that one needs to understand the sport to be able to solve this problem. This is mostly a sorting issue. I can visually look at the spread sheet (sheet1) and identify that Gordon should be placed at 3B and Morneau should be at 1B, but don't really know how to get Excel to come to that conclusion. By they way, an optimal lineup would be a lineup that scores the most points.

The entire spread sheet for solving the optimal lineups is too large to post in this forum. If anyone thinks they might be able to do this I could email you the whole sheet that shows my current progress.

-jed

i know what baseball is! i've even watched a bit on tv. (i was only joking)
but although its clear to you looking at sheet its very difficult for us to visualise it as you do. i'll pm you with my email send it to me and i'll see what i can do

ok i had a look but what if
Hart, Corey C. RF MIL A DH OF 0 32
Hunter, Torii CF ANA A OF OF 2 31
were
Hart, Corey C. RF MIL A DH OF 0 32
Hunter, Torii CF ANA A OF OF 2 32

how do you choose between them for OF ?

That was one of the issues that was a big problem, but I think that when the data is brought in from the external website, they are already sorted. So in the situation you brought up where the two players have the same points, then it would be the player listed first that would have priority.

The only other way I might do it differently is if two players are tied, then the active player (A) would have priority over the reserve player (RS), but I think that might be much more hastle than it's worth.

-jed

thanks for all the help. I did manage to solve for the optimal lineups. it took 426 columns and increased the excel file I use to create my weekly reports from 31 MB to 132 MB.

-jed