How do you install Excel Solver in Excel?

Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model

Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Load the Solver Add-in

To load the solver add-in, execute the following steps.

1. On the File tab, click Options.

2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

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

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out how much to order of each product (bicycles, mopeds and child seats).

b. What are the constraints on these decisions? The constrains here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. For example, each bicycle uses 300 units of capital and 0.5 unit of storage.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2. To make the model easier to understand, create the following named ranges.

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter OrderSize for the Changing Variable Cells.

5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

Result:

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available. Try it yourself. Download the Excel file, enter the solver parameters (previous 7 steps) and find the optimal solution.

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

()

The Excel Solver plug-in allows you to find the minimum and maximum figures for a potential calculation. Here’s how to install and use it.

There aren’t many mathematical problems that can’t be solved by Microsoft Excel. It can be used, for instance, to help solve difficult what-if analysis calculations using tools like Goal Seek, but better tools are available.

If you want to find the lowest and highest numbers possible to answer a mathematical problem, you’ll need to install and use the Solver add-in. Here’s how to install and use Solver in Microsoft Excel.

What is Solver for Excel?

Solver is a third-party add-in, but Microsoft includes it with Excel (although it’s disabled by default). It offers what-if analysis to help you determine the variables you need to answer a mathematical problem.

For instance, what is the minimum number of sales you’d need to make to cover the cost of an expensive piece of business equipment?

This problem has three parts to it—a target value, variables that it can change to reach that value, and constraints that Solver has to work in. These three elements are used by the Solver add-in to work out the sales you’d need to cover the cost of that equipment.

This makes Solver a more advanced tool than Excel’s own Goal Seek feature.

How to Enable Solver in Excel

As we’ve mentioned, Solver is included with Excel as a third-party add-in, but you’ll need to enable it first to use it.

To do that, open Excel and press File > Options to open the Excel Options menu.

In the Excel Options window, press the Add-ins tab to view the settings for Excel add-ins.

In the Add-ins tab, you’ll see a list of available Excel add-ins.

Select Excel add-ins from the Manage drop-down menu at the bottom of the window, then press the Go button.

In the Add-ins window, press the checkbox next to the Solver Add-in option, then press OK to confirm.

Once you click OK, the Solver add-in will be enabled, ready for you to begin using it.

Using Solver in Microsoft Excel

The Solver add-in will be available for you to use as soon as it’s enabled. To start, you’ll need an Excel spreadsheet will appropriate data to allow you to use Solver. To show you how to use Solver, we’ll be using an example mathematical problem.

Following on from our earlier suggestion, a spreadsheet exists to show the cost of an expensive piece of equipment. To pay for this equipment, a business needs to sell a certain number of products to pay for the equipment.

For this query, several variables could change to meet the target. You could use Solver to determine the product cost to pay for the equipment, based on a set number of products.

Alternatively, if you’ve set the price, you could determine the number of sales you’d need to break even—this is the problem that we’ll be attempting to solve using Solver.

Launching Solver in Excel

To use Solver to solve this kind of query, press the Data tab on the Excel ribbon bar.

In the Analyze section, press the Solver option.

This will load the Solver Parameters window. From here, you can set up your Solver query.

Choosing Solver Parameters

First, you need to select the Set Objective cell. For this scenario, we want the revenue in cell B6 to match the equipment cost in cell B1 to break even. From this, we can determine the number of sales we’d need to make.

The To figure lets you find the minimum (Min) or maximum (Max) possible value to meet a target, or you can set a manual figure in the Value Of box.

The best option for our test query would be the Min option. This is because we want to find the minimum number of sales to reach our break-even target. If you wanted to do better than this (for instance, to make a profit), you could set a custom revenue target in the Value Of box instead.

The price is remaining the same, so the number of sales in cell B5 is the variable cell. This is the value that needs to increase.

You’ll need to select this in the By Changing Variable Cells option box.

You’ll need to set constraints next. These are the tests that Solver will use to help pick its final value. If you have complex criteria, you can set multiple constraints for Solver to work in.

For this query, we’re looking for a revenue number that’s greater than, or equal to, the initial equipment cost. To add a constraint, press the Add button.

Use the Add Constraint window to identify your criteria. For this example, cell B6 (the target revenue figure) needs to be greater than, or equal to, the equipment cost in cell B1.

Once you’ve selected your constraint criteria, press the OK or Add buttons.

Before you can run your Solver query, you need to confirm the solving method for Solver to use.

By default, this is set to the GRG Nonlinear option, but there are other solving methods available. Once you’re ready to run the Solver query, press the Solve button.

Running a Solver Query

Once you press Solve, Excel will attempt to run your Solver query. A results window will appear to show you whether or not the query was successful.

For our example, Solver found that the minimum number of sales required to match the equipment cost (and thus break even) was 4800.

You can select the Keep Solver Solution option if you’re happy with the changes Solver has made, or Restore Original Values if you’re not.

To return to the Solver Parameters window and make changes to your query, press the Return to Solver Parameters Dialog checkbox.

Click OK to close the results window to finish.

Working with Excel Data

The Excel Solver add-in takes a complex idea and makes it a possibility for millions of Excel users. It’s a niche feature, however, and you may be looking to use Excel for much simpler calculations.

You can use Excel to calculate percentage changes or, if you’re working with a lot of data, you can cross-reference cells across multiple Excel worksheets. You can even insert Excel data into PowerPoint if you’re looking for other ways to use your data.

Microsoft, Microsoft Excel, Microsoft Office