In this tutorial, I am going to show you how to use the **Evolutionary **solver in excel to solve complex optimization problems. You can use this solver in numerous real-life cases where other excel solvers may not be applicable. Also, throughout this tutorial, you will learn some important topics like scatter charts, various functions, etc.

## Download Practice Workbook

You can download the practice workbook from here.

## Step-by-Step Procedures of How to Use Evolutionary Solver in Excel

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.

### Step 1: Creating Data Tables

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)`

- Next, copy this formula to all the cells below using
**Fill Handle**which will give the**Depot Distances**for all the stores.

- Now, insert the data in the other tables as shown in the image below.

### Step 2: Generating Scatter Plot

To get a visual idea of the locations of the stores, we will plot them on an **excel** scatter chart. Follow the steps below to do this.

- To begin this step, select the cells from
**C5**to**D10**. - Now, navigate to the
**Insert**tab and select**Scatter Chart**.

- As a result, this will generate a
**2D Scatter Plot**showing the relative position of the stores.

### Step 3: Setting Up Solver

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.

- For this, 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**.

### Step 4: Finding Solution

Once we have set up all the parameters, now we are 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**.

- Similarly, the solver should calculate the data of the first table as well.

## Comparing Evolutionary and GRG Nonlinear Solvers in Excel

The **GRG Nonlinear **solver in excel works by trying to find a point at which the slope of the function is zero. This is to make sure that the function reaches either a maximum or a minimum value at that point. But if there are any non-smooth functions in the target cell like **MAX**, **MIN**, **SUMIF**, **COUNTIF**, etc, then for them we canâ€™t use the **GRG Nonlinear **solver.

On the other hand, the **Evolutionary **solver doesnâ€™t rely on finding slope. So it can not determine whether a given solution is optimal or not. It only knows that a new candidate solution is better than previous ones. So it stops calculation when a certain set criterion is achieved.

## Conclusion

I hope that you were able to apply the above steps to solve a problem using the **Evolutionary **solver in excel. Although we have used a relatively simple problem for this tutorial, you can use these steps to solve more complex real-life problems as well. If you get stuck at any steps, I recommend going through the steps a few times to clear up any confusion. Lastly, to learn more **excel** techniques, follow our **ExcelDemy** website. If you have any queries, please let me know in the comments.