How to Use Slver 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:

Advanced Excel Solver Techniques

Sensitivity Analysis with Solver:

Sensitivity analysis allows you to assess the impact of changes in variables and constraints on the optimal solution. By adjusting certain inputs and running Solver multiple times, you can understand how sensitive the solution is to different factors. This analysis helps in understanding the robustness and reliability of the optimal solution.

Understanding Shadow Prices and Reduced Costs:

Shadow prices, also known as dual values, provide insights into the impact of changes in constraints on the objective function. They represent the rate of change in the objective function value per unit change in the constraint. Reduced costs indicate how much the objective function value would change if additional resources were available. Understanding these values helps in evaluating the importance of constraints and resource allocation.

Nonlinear Problems and the GRG Nonlinear Solver:

The GRG Nonlinear Solver is specifically designed to handle problems with nonlinear objectives, nonlinear constraints, or both. It employs the Generalized Reduced Gradient (GRG) algorithm to find the optimal solution. This solver method is suitable when dealing with complex mathematical functions, allowing you to solve a wide range of nonlinear optimization problems.

About WPS

WPS Spreadsheet, often known as WPS Excel, is a component of the WPS Office suite, a free alternative to Microsoft Office. It is intended to give an experience comparable to Microsoft Excel, with a variety of capabilities for generating, editing, and maintaining spreadsheets.

Furthermore, I encourage you to visit our websites: the official online academy of WPS Office, a well-known office software package. It provides a number of free lessons and tools to assist users in improving their abilities with WPS Office products such as WPS Writer, WPS Spreadsheet, WPS Presentation, and PDF.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

FAQs

What are the Limitations of Excel Solver?

One of the limitations of Excel Solver is that it has a maximum limit of 200 decision variables when dealing with multiple constraints. This means that if your optimization problem involves more than 200 decision variables, Excel Solver may not be able to handle it effectively.

What if Solver can't Find a Feasible Solution to My Problem?

If you run it with alternative beginning values for the variables and/or raise the Precision value in the Solver Options box (which minimizes the infeasibility penalty, enabling the evolutionary method to explore more "nearly feasible" locations), it could locate one.

How does the Evolutionary Algorithm Differ from the GRG Nonlinear Algorithm?

The Evolutionary algorithm can indeed be more robust in finding a globally optimum solution compared to the GRG Nonlinear algorithm. Its ability to explore a wide solution space and its ability to handle complex problems with nonlinearities make it suitable for finding global optima. However, it is important to note that the trade-off for this robustness is the computational time required.

Conclusion

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.