How to Assign Work Using Evolutionary Solver in Excel

An evolutionary solver can help you to assign work. By default, you don’t get the solver option in your Microsoft Excel. You have to enable the solver option through the Add-ins. After that, you can use the solver option. This article will show you how to assign work using an evolutionary solver in Excel. I hope you find this article very informative and gain lots of knowledge.

Download Practice Workbook

Download the practice workbook below.

Overview of Excel Evolutionary Solver

The solver tool is used to find an optimum value for a formula in one cell, by changing decision variables. While solving linear programming problems the solver tool is essential. To deliver a solution, Solver needs a group of cells called the variable cells whose values are adjusted, to meet the target in the objective cell. These variable cells are subject to constraint criteria. Excel Solver has many different algorithms for finding the solution to different complexes linear and non-linear problems such as GRG Nonlinear, LP Simplex, and Evolutionary.

How to Use Evolutionary Solver in Excel

We can use the evolutionary solver for many real-life cases. We have taken a concise dataset to explain the steps of this tutorial. This dataset has 6 rows and approximately 11 columns. Here we want to distribute the depots in a way so that the Total Distance becomes minimum.

Steps

• In this first step, we will insert the data in a table format which the excel Evolutionary solver will need to perform calculations. First, insert the data of columns Store ID, X-Coord, Y-Coord, and Depot Assigned manually from the keyboard. Then, click on cell F5 and enter the following formula:

• Then, press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• We need to set up the solver to perform the calculations properly. Here, we will set the objective cell, the changing cells, and all the other constraints. First, go to the Data tab and click on Solver.

• Immediately, the Solver Parameters window will now open.
• Next, insert the Set Objective, To, and By Changing Variable Cells fields as in the image below.
• Now, to add necessary constraints, click on Add.

• Here, in the new Add Constraint window, fill the 3 required fields and click Add after entering a constraint.
• Then, once you have entered all the constraints, click OK.

• Once we have set up all the parameters, we are now ready to perform the calculations. We will choose the Evolutionary method to solve this problem.
• Now, you can see that we have entered all the necessary 7 constraints as below.
• Then, set Evolutionary as the Solving Method and click Solve.

• Consequently, this will give you the required solution and show the window below.

• So, as you can see, the solver calculated the Total Distance and coordinates of the Depots.

Step-by-Step Procedure to Assign Work Using Evolutionary Solver in Excel

To assign work using an evolutionary solver in Excel, we will show the step-by-step procedures through which you can have a clear idea. First, you need to understand the problem clearly. Suppose that we need to assign 40 employees to four Departments. The head of each department has rated each employee’s competence on a 0 to 10 scale (10 equals most competent). Each employee has rated his satisfaction with each job department (again on a 0 to 10 scale). Data are recorded in the Problem worksheet. we are going to assign between 8 to 12 people to each department. The problem is how you can assign employees to workgroups to maximize total satisfaction and ensure that each division has the required number of employees. Follow the steps carefully.

Step 1: Enable Solver in Excel

By default, you don’t get the solver option in your Microsoft Excel. You have to enable the solver option through the Add-ins. After enabling the solver add-ins, you will get them in the data tab.

• First, go to the File tab on the ribbon.
• Then, select the More command.
• After that, select Options.

• The Excel Options dialog box will appear.
• Then, select Add-ins.
• After that, select Excel Add-ins from the Manage section.
• Then, select Go.

• After that, the Add-ins dialog box will appear.
• Then, check on the Solver Add-in from the Add-ins available section.
• Finally, click on OK.

• As a consequence, you will get the Solver option from the Data tab on the ribbon.

Step 2: Find Out Qualification and Satisfaction Value Using HLOOKUP Function

Here, in the dataset, we have 40 workers and their individual qualifications and satisfaction in the four different departments. We would like to define the possible formula to get qualification and satisfaction so that when we assign any department, the qualification and satisfaction column will fill up automatically. Follow the steps.

• First, select cell L6.
• Then, write down the following formula using the HLOOKUP function.

• Press Enter to apply the formula.

• Now, if you assign worker 1 to department 1, the qualification will change from #N/A to a certain value.

• Then, drag the Fill Handle icon down the column.
• After that, we would like to define the formula for the satisfaction column.
• First, select cell M6.
• Then, write down the following formula.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column to use a similar formula for all the cases.
• When we assign the department, we will get the satisfaction of that worker by using this formula.

Step 3: Calculate Total Satisfaction

Our third step is based on calculating the total satisfaction. In this step, we will add all the satisfaction of 40 workers using the SUM function.

• First, select cell P12.
• Then, write down the following formula.

• Press Enter to apply the formula.

Step 4: Calculate Total Assigned Number of Each Department

Our next step is to calculate the total assigned number of each department. To do this, we would like to use the COUNTIF function. After that, we also calculate a condition using the combination of IF and OR functions. Follow the steps.

• First, create a new column having 4 departments.
• Then, in the column, we would like to calculate the total assigned number of each department.
• Select cell P7.
• Then, write down the following formula.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column.

• After that, we would like to insert conditions in the next column.
• Select cell Q7.
• Then, write down the following formula.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column.

Step 5: Utilize Evolutionary Solver

Our last step is to utilize the evolutionary solver in Excel. We need to set up the solver to perform the calculations properly. Here, we will set the objective cell, the changing cells, and all the other constraints.

• First, go to the Data tab and click on Solver.

• Immediately, the Solver Parameters window will now open.
• Next, insert the Set Objective, To, and By Changing Variable Cells fields as in the image below.
• Now, to add necessary constraints, click on Add.

• Here, in the new Add Constraint window, fill the 3 required fields and click Add after entering a constraint.
• Then, once you have entered all the constraints, click OK.

• Once we have set up all the parameters, we are now ready to perform the calculations. We will choose the Evolutionary method to solve this problem.
• Now, you can see that we have entered all the necessary 4 constraints as below.
• Then, set Evolutionary as the Solving Method and click Solve.

• Consequently, this will give you the required solution and show the window below.
• Click on OK.

• As a result, you will see total satisfaction.
• Also, you will see the evolutionary solver assigned department for the workers.
• As all of our formulae depends on the assigned department, you will get satisfaction, qualification column is filled up because of it.
• As a result, you also get the total number of assigned workers for a certain department.

Conclusion

We have shown step-by-step procedures to assign work using an evolutionary solver in Excel. We have also included a detailed overview of the evolutionary solver and how to enable the solver using the Excel add-ins. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.

Read More…

Sequencing problem using Johnson’s algorithm of scheduling n-jobs on 2-machines [Sol]

GRG Multistart and Evolutionary Excel Solver Engines [2 Case Studies]

How to Use Solver in Excel (Solving Linear Programming Problems)!