The variable cells will be changed by Solver subject to meeting our 4 criteria defined aboveĪ. This is the Variable Cells $E$2:$G$25 Subject to the Constraints We will be changing the allocation of players into each team. That is to Minimize Cell E27 Variable Cells The Objective is to Minimise the Sum of the Team Scores Lets look at how our model is setup in Solver Objective It measures the output and re-iterates until a better solution is reached. Simplistically it iterates values into the Variable Cells, subject to meeting the constraints. Solver operates using a number of techniques to Solve the above problem. We have a Number of Constraints that our model will be subject toĮach player must have a 1 in a Column of Team 1, Team 2 or Team 3Īll 24 Players must be used only once eachĮach player can only be in a Team, he can’t be shared between teams That is Each player must have a 1 in a Column of Team 1, Team 2 or Team 3 Subject to the Constraints We want to achieve our objective by setting Each Player to be a Member of 1 team In our Even Teams example we want to minimise the variance in the average Team Scores By Changing variable Cells Solver is asking us to Set our Objective, to a Minimum, Maximum or Value, by changing some cells, Subject to some constraints. Lets look at each of the highlighted sections first and I will discuss this first as a plain English and then I will discuss how it is implemented in Solver Solver will now be visible in the Data, Analyze Tab Manage Excel Add-ins in the Manage Dialog and press Go… If you cannot see it, you may not have Solver Loaded. Your screen may look different to mine depending on which version of Excel you are using and if you have your Excel window at a maximum size or not. Solver is found in the Data, Analyze Tab. We should be able to get Solver to Minimise this value. Next add a Formula to Calculate the Sum of the Variations from Mean for each Team Solver will put a value of 1 when a Player is in a Team, and a 0 when the player is not in a Team. Next we need to distribute each player into one of 3 teams. Then calculate the Differences between the each players Score and the Average My initial though was to setup a Delta or Difference between each Players Score and the Mean (Average of all scores).įirst calculate the Average of All the Scores The question posed by Shenricus is to distribute the players into teams so that each team is “As even as possible”.Ĭonsidering that we have 24 players and need to put them into 3 teams, we will assume each team has the same number of players and hence requires 8 players. We have no other information as to the Sport or Score.
The players are Ranked from Best to Worst. Shenricus gave us a list of 24 players and a score for each player.
COLORS USED IN SOLVER FOR EXCEL DOWNLOAD
This post will examine the thought process used to derive the solution and then implement that using solver.Īs always a Sample file is provided so you can follow along: Download Sample File here. This caused me to reconsider my first attempt and finally I posted a Final Solution, which was also a Solver based solution, but was a much more robust solution than my original solution or Bosco Yip’s solution. I answered with a Solver Based solution, and Bosco Yip also added to my solution with a slightly different approach. I’ve been trying to figure out how I can divide these names into 3 even teams – or as close as possible.”
“I have 24 people who each have their own score. In April 2017, Shenricus, posed a question in the Forums:
0 kommentar(er)
