How to use Solver in Excel
It is important to learn different parts of the Solver parameter dialog box before we use it 🧑🏻🎓
The set objective is the first input for the Excel Solver parameter dialog box.
In the set objective box, we have to select the cell reference for the objective cell.
The objective cell must have a formula.
Then you can set the objective cell to one of the following.
- Value of – If you select “Value of”, you must specify the target value in the box.
By changing variable cells
In this “by changing variable cells”, you have to select all the variable cells to get the optimal solution.
The variable cells are directly or indirectly related to the target cell.
Currently, you can include up to 200 variable cells for the Excel solver function.
If the variable cells are non-adjacent cells, you have to separate them with commas.
Subject to the constraints
Excel Solver can find the optimal solution within constraints or limits.
We can add constraints in this constraint box.
To add constraints, click add button.
Then “Add constraint” box will pop up.
You can select the variable cell for the cell reference box and add the limit in the constraint box.
You must specify the relationship between the cell reference box and the constraint box also.
Do you want to delete or change an existing constraint? 👇🏻
Select that constraint from the Excel Solver parameters dialog box.
Then click the change button to change the existing constraint.
Or click the delete button to delete that.
Select a solving method
In Excel solver, we can select one of the solving methods from the following 3.
- GRG non-linear – For solver problems that are smooth nonlinear.
- Simplex LP – For linear programming problems.
- Evolutionary – For solver problems that are non-smooth.
Now you are familiar with the Excel solver add-in options window 💪🏻
Let’s use Excel Solver to find a solution for a linear programming model.
Linear programming with Solver add-in
In the below example, we have to find how many units we have to sell in order to get the desired outcome of profit.
Let’s learn step by step how to use the Excel Solver solution to our linear programming problem.
- Go to the data tab and click solver to open the Excel Solver.
- Select the objective cell reference.
Our goal is to set the total profit in cell B7 to $900.
So, B7 is the objective cell.
As we need to match the value of the objective cell to $900, we have to select the option of “Value of”.
Then type 900, next to the “value of” option.
- Select the variable cells.
The variable for the target value is the quantity for each cell.
So, in this case, the variable cells are B2, C2, and D2.
- Click the “Add” tab to add constraints for each variable.
Choose each product’s quantity cell as a cell reference.
Then choose the referenced cell’s maximum amount as the constraint.
Next, choose the link between the referred cell.
Here we select “less than or equal sign” because the constraint is maximum quantity.
Again, we need the quantities as whole numbers.
So, we set another constraint for each variable cell as an Integer.
After entering all constraints, the solver displays all constraints for the particular solution.
- Select the “Simplex LP” as we are solving a linear solver problem and click the “Solve” button at the bottom of the window.
- Then, you can see the Solver results dialog box with options to “keep solver solution” and to “restore original values”.
Select the “keep solver solution” and click “OK”.
To get a total profit of $900, we have to sell 149 in vanilla flavour, 114 in chocolate flavour, and 75 in banana flavour.