Excel Solver Tutorial

Excel Solver Tutorial

https://softtechtutorials.com/microsoft-office/excel/excel-solver/

0:00 Introduction 0:25 Add Excel Solver Add-in 0:47 Magic Square Example 2:04 Adding Constraints 4:57 Solver Result 5:23 Solving Methods 5:27 Construction Example 10:33 Mathematical equation

In this video, we will explain how to use Excel’s solver. This is a tool that tries to find optimal solutions for all kinds of decision problems. The tool can be used by loading the Excel Solver add-in. First, we will show how you can do this. After that, we dive into its functionality.

We start with a magic square example. This is a three by three square consisting of the numbers 1 up to 9 that satisfies a few rules:

First, all numbers 1 up to 9 should be present in the square Second, the sum of all rows should be 15 Third, the sum of all columns should be 15 And finally, the sum of the up-diagonal and down-diagonal elements should be 15 as well

You navigate to the Data tab and click on the Solver tool. The first element to enter is the objective.

Next, we have to indicate in which cells we would like the values to be changed. Beneath the “changing variable cells”, we have the potential constraints we can add. In this case, we want that the sum of each row, each column, and each diagonal is 15, the square elements should all be different and it should be integer values.

First, we make sure all row sums are 15. To do that, we select the cells containing the row sums as cell references. Then we choose the equal sign and then we fill in 15 in the constraint box.

When all constraints are implemented, there are still two more functionalities to consider. First, you can choose to make unconstrained variables non-negative. The interpretation of this functionality is straight-forward.

Finally, you have three options you can choose as a solving method: GRG Nonlinear, Simplex LP, and Evolutionary. Each one has its own pros and cons. For now, we change the Solving Method to Simplex LP.

The first function you see is a linear one. This can be solved with all three methods, however, Simplex LP is specially made for these types of problems and is the fastest and most efficient to find a solution.

The second function you see is a non-linear function with only one optimum. This can be solved by the GRG Nonlinear and Evolutionary methods. Here, the GRG Nonlinear method will be the fastest and most efficient way to come to a solution.

The third function you see is a smooth non-linear function with two optima.

The last function you see on this sheet is a non-smooth non-linear function with two optima. Because of its lack of smoothness, this problem can only be solved by the Evolutionary solving method.

With this we conclude our Excel Solver tutorial. I'm inspired ... https://www.youtube.com/watch?v=STY9ZQ5BhDk