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

## 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. To do that, we will use Excel Solver for Linear Programming. 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 minimal.

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

## How to Assign Work Using Evolutionary Solver in Excel: with Easy Steps

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

**Read More: **Example with Excel Solver to Minimize Cost

### 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 for 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 the 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 depend 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.

**Read More:** How to Do Portfolio Optimization Using Excel Solver

**Download Practice Workbook**

Download the practice workbook below.

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

## Related Articles

- How to Use Excel Solver to Rate Sports Team
- How to Use Excel Solver to Determine Which Projects Should Be Undertaken
- Solving Sequencing Problems Using Excel Solver Solution
- Solving Transportation or Distribution Problems Using Excel Solver
- Resource Allocation in Excel
- How to Create Financial Planning Calculator in Excel

what a great work of you ! Could you please upgrade and combine this model so that it can also solve the sequencing problem ?

Hi BP,

Thank you for your comment. To solve the sequencing problem, you may go through these two articles,

1. Solving Sequencing Problems Using Excel Solver Solution2. Sequencing problem using Johnson’s algorithm of scheduling n-jobs on 2-machinesPlease go through these articles. If you have any further queries then please inform us in the reply.

Thanks and regards,

ExcelDemy Team