How to use the solver in excel (Easy Steps)

July 26, 2022
1.1K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

Excel Solver is an optimization tool that may be used to find ways to modify a model's assumptions in order to obtain the desired result. It is a form of what-if analysis and is especially helpful when attempting to identify the optimal outcome in light of a number of assumptions.

How to use solver in excel online, 2016 and 2019:

The ideal way to demonstrate how to utilize the Excel solver is to graph a scenario where, for instance, there is a non-linear relationship between the number of salesmen employed by a company and the profit they make.

We want to determine the ideal amount of salespeople to hire because there is a declining return on salespeople. To put it another way, we want to determine the optimal number of salesmen to hire in order to maximize profit.

Install the Solver Add-In, if necessary.Making sure Solver is installed in your Excel file is the first step. Navigate to File -> Options -> Add-Ins -> Manage Excel Add-Ins to accomplish this. Make sure the box is checked when the dialogue box appears as seen below.

Build a model.Here, we've developed an illustration of a non-linear relationship between a company's earnings and the number of salespeople working there. This is due to the fact that as more salespeople are added, their overall effectiveness decreases. Consider a market that caters to a small number of customers.At first, adding more salespeople increases income significantly, but eventually, as they run out of prospects to approach, more salespeople become a burden on the business. This model's goal is to provide an example that is difficult to calculate using Goal Seek or another method.

Excel Solver may be launched using the ribbon.The shortcut for the Excel Solver function on Windows is Alt, A, Y21, and it may be found on the Data Ribbon. When the dialogue box displays, you'll see a number of alternatives you can choose from.Set the cell for Objective. Profit in our situation is this.Set a value for To (Max, Min, or a specific value). Our goal is to maximise profit.To obtain the answer, choose the Cell(s) that you want to modify. In this instance, we want to choose the salesperson count cell.Add limitations. You can use this if you want to create a constraint (for instance, a cell must be > or a certain number). There are no restrictions in our situation.Click on solve.Choose whether you want to leave the answer in the cells or change everything back to how it was.

Graph solution in excel:The relationship between the number of salespeople and the business's profit is clearly illustrated in this example by the use of a graph to obtain the solution. The graph and Excel Solver together truly give our analysis a lot of credibility.

Note: This above written article is an attempt to show you how to use solver in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.