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.

**Table of Contents**hide

## 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:

`=SQRT((C5-OFFSET($I$4,E5,0))^2+(D5-OFFSET($J$4,E5,0))^2)`

- 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**.

`=HLOOKUP($C6,$D$5:$G6,$B6+1,FALSE)`

- 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.

`=HLOOKUP($C6,$H$5:$K6,$B6+1,FALSE)`

- 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.

**🔎 Breakdown of the Formula **

**HLOOKUP($C6,$H$5:$K6,$B6+1,FALSE):** The **HLOOKUP** function searches for a value in the top row of a table or an array and then returns a value in the same column from a row you specify in the table or array. Suppose that we’d like to search value 1 (from cell **C4**) in the first row (**D3:G3**) of the table (**D3:G5**), in which column can we find value 1? The answer is “Column** D**”. Now let’s look at Column **D**. There are **3** values in total. What value will you get if you want to retrieve data from the second row of the table? It is **9**.

### 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.

`=SUM(M6:M45)`

- 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.

`=COUNTIF($C$6:$C$45,$O7)`

- 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.

`=IF(OR(P7<8,P7>12),1,0)`

- 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…*

*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)!**