How to Use Excel Evolutionary Solver (With Easy Steps)

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.

excel solver evolutionary


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.

input values in table to use excel evolutionary solver

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

scatter plot from the data to use excel evolutionary solver

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

opening excel evolutionary 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.

setting parameters of excel evolutionary solver

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

setting method as excel evolutionary solver

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

completed excel evolutionary solver result

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

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo