In a previous blog post, I made a video showing how to run an optimal lineup in Excel using Solver.  That post is here.  That post showed a straight forward lineup without a flex spot.  However, some websites require you to pick a flex or utility spot.  That makes it a little trickier.

Click here to download the spreadsheet

I’m not going to go over every step again of setting this up for Solver. But I will describe the main differences. The main difference is to change the Solver requirements for each position. Say we want to add one utility spot. Instead of entering into Solver an exact match, we need to make a minimum and maximum.

For example, for 1B, instead of saying we want one first baseman, we will say we want a minimum of 1 but a maximum of 2 first basemen. Then you have to set that up for all the positions. In addition, you need to make sure the total number of players is updated. This way Solver will pull the correct number of players and find which positional player will be chosen for the utility spot.