Scalable baseball lineup from stats

Originally Posted by 6StringJazzer

I don't have a solution for you but I have some suggestions as to how to formulate your request to get a better response. First,

Even though you may not know how to solve any part of your problem, it would be helpful to provide a workbook with sample data, and manually set up the data to illustrate the result you are looking for. A "before & after" view would be ideal.

Not sure about before and after, but I have attached a wb that includes the stats I'd use and a sample lineup with the rules for each spot.

Originally Posted by 6StringJazzer

I am not quite sure how you want to use these rules. Do you mean that you want to use each rule to select the single player that meets that rule? That is, you'll end up with one player for #1, one player for #2, etc., and they will be four different players? When you referred to an ordered list it sounded like you wanted to sort a list of players, but that's not the same as four players. And four players is not a lineup. So that's why I'm a little unsure as to exactly what result you are trying to get.

I've coached youth baseball so I'm familiar with your stats but I want to firm up where you want to go before spending any effort in the wrong direction.

Yes, each rule to select a single player that meets the requirements, no duplicates obviously. I hope this makes sense?

Thanks for your reply!

Ray

I have been looking at this and don't have a solution for you yet but my current thinking is that it is going to use VBA (macros). The logic is such that it will be easier for me to do it with VBA than formulas, although there are some real formula wizards around here that might be able to pull it off.

Meantime, I am confused just a little about your ranking criteria. Are you taking an existing team, and then optimizing the batting order?

Originally Posted by 6StringJazzer

Meantime, I am confused just a little about your ranking criteria. Are you taking an existing team, and then optimizing the batting order?

That would be the plan, yes. I have a scoring app that I use in place of a paper scorebook, and it keeps batting and pitching stats. So I want to be able to bring the stats into Excel and have it set up the lineup per the Sabermetric rules I mentioned above.

OK, here's a solution. [All Star Lineup.xlsm] My results do not exactly match yours, but my initial analysis is that you may have made an error in following your specification. For example, the first mismatch is in slot #7, where you have Alena and I have Sammy. The criterion is best OPS that is not best SLG, but Alena has the best SLG of the remaining players so she doesn't qualify. I didn't follow up for the entire list.

Naturally you must allow macros to run. Push the "Create Lineup" button to refresh the lineup.

That is one amazing bit of kit right there! Thanks a ton! Rep added!

The error is caused by putting "---" into cells where a number is expected. Your scorekeeping app is putting in "---" for some stats when the player has no at-bats. Technically the stat is undefined if there are no at-bats but I suggest you put zeroes into those. That will cause you to underrate players like Syd with 3 walks and no hits, who has a perfect on-base percentage but will show a .000 batting average, but hopefully that will smooth out over the season.

Man, I'm a dope. Didn't even look at that as a possibility. Thanks so much AGAIN!

Ray

Glad to help! Come back any time!

Originally Posted by lavudyar

Man, I'm a dope. Didn't even look at that as a possibility. Thanks so much AGAIN!

Ray

Has this been developed any further? Curious if you’d consider sharing it.

Originally Posted by mrpeepers

Has this been developed any further? Curious if you’d consider sharing it.

The only thing I did to modify the sheet was to add a tab for pasting my scorebook info, and then used vlookup to populate the master tab. The best part is that you can change the far right column to be whatever stat you like. I started with OPS and ended using wOBA (not true wOBA as I did not have access to the league averages; I used MLB modifiers). The same 2014 sheet above is still valid as is.

Awesome! I LOVE it, thanks for sharing.

Hey mod, I think our definitions of "hijacking" are a bit different. This thread was dated, so, I think it's a legitimate question to ask the OP if he'd developed his/her solution since the 6 years that have passed since the OP.

Originally Posted by mrpeepers

Awesome! I LOVE it, thanks for sharing.

Do not change the title of the OPS column though! The VBA looks for that specifically and you will break the sheet. I haven't dug into that, probably an easy fix but it doesn't matter because it sorts based on that column no matter what numbers end up in it.

Got it, and thanks. No need, I just edited the data — that's it (imported from Game Changer) and it works like a dream