How to use solver in Excel (A Complete Guide)

If you want to learn how to use Solver in Excel, you should know that it is an easy function. Solver is an Excel program that you can use to find an optimal value (minimum or maximum) for a formula in a cell.

It is a function that works with a set of cells called variable cells and allows you to calculate the formulas in the constraint cells and the target cells. Below you can learn more about how to use Solver in Excel.

How to use Solver in Excel?

Excel is a powerful tool that includes various functions, such as Solver, which uses operations research techniques to find efficient solutions for all kinds of decision problems.

In addition, Solver allows you to adjust the values in the decision variable cells to produce the desired result and satisfy the constraint cell limits for your target cell.

To load the Solve plugin and learn how to use Solver in excel 2016, you will need to perform the following steps:

Open your worksheet, and in the File tab, you need to click on Options.

Then, in plugins, you will need to select Solver Plugin and click on the Go button.

When you have marked Solver Add-in, you must click on OK and continue with the process.

You can find Solver in the Data tab, in the Analyze group.

How to use Solver in Excel to formulate the model?

With the steps mentioned above, you learned how to correctly use Solver in excel on. Now you will know how to formulate the model in Excel.

For this example, the model to be solved looks like this in Excel.

To formulate this linear programming model, you must take into account the following aspect:

What will be the decision to be made? For this example, we need to know how much to order for each product.What are the limitations of these decisions? At this point, you will need to know the amount of capital and storage used.What is the overall measure of performance for these actions?

2. To continue with this example of how to use Solver in excel 2019 the following named ranges create:

3. You will need to insert the following three SUMPRODUCT functions.

Note: The amount of capital used is equal to the sum of the product of the range C7:E7 and OderSize. The total profit is equal to the sum of the product of UniProfit and order size. At the same time, the amount of storage used is equal to the sum of the product of the range C8:E8 and OderSize.

How to use Solver in Excel to quickly find the optimal solution?

Following the formulation, you will also be able to analyze any trial solution. For example, if you need to order 20 bicycles, 100 child seats, and 40 mopeds, the total amount of resources used does not exceed the number of resources available. This solution will have a total profit of 19000.

Note: You will not need to use trial and error.

But if you need to find the optimal solution, you can follow the steps below:

1. On the Data tab, in the Analyze group, you need to click on Solver.

2. Next, you will need to enter the solver parameters. The result should be consistent with the image below.

You will have the option to type the names of the ranges or click on the cells in the worksheet. You will then need to enter TotalProfit for the target and click on Max.

3. You will need to enter OderSize for the changing variable cells. To continue with the process of how to use Solver in excel mac, you will need to click. The following restrictions will enter.

4. Check Make unrestricted variables non-negative and select Simple LP. Finally, click on Solve. The result will be the following:

5. The optimal solution will be:

Have you learned the correct way how how to use Solver in Excel? They are simple methods that you can follow without being an Excel expert and will allow you to broaden your working knowledge.

If you want to learn other special features of Excel, you can also follow WPS Academy. In addition, you can download WPS Office to create, process, and edit Excel, PowerPoint, and Word documents for free.