Solver Excel How Does It Work

Solver

Excelincludes a tool calledsolverthat 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

Themodelwe are going tosolvelooks 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, name the following ranges.

Range NameCells
UnitProfitC4:E4
OrderSizeC12:E12
ResourcesUsedG7:G8
ResourcesAvailableI7:I8
TotalProfitI12

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 theoptimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, clickSolver.

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.

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.

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.

Excel Solver: What is It and When You Should Use It

Excel Data Analysis For Dummies, 4th Edition

Excel spreadsheet tools such as Goal Seek that change a single variable are useful, but unfortunately most problems in business are not so easy. You’ll usually face formulas with at least two and sometimes dozens of variables. Often, a problem will have more than one solution, and your challenge will be using Excel to find theoptimalsolution (that is, the one that maximizes profit, or minimizes costs, or matches other criteria). For these bigger challenges, you need a more muscular tool. Excel has just the answer: Solver. Excel Solver is a sophisticated optimization program that enables you to find the solutions to complex problems that would otherwise require high-level mathematical analysis.

Understanding Excel Solver

Solver, like Goal Seek, uses an iterative method to perform its calculations. Using iteration means that Excel Solver tries a solution, analyzes the results, tries another solution, and so on. However, this cyclic iteration isn’t just guesswork on Solver’s part. That would be silly. No, Excel Solver examines how the results change with each new iteration and, through some sophisticated mathematical processes (which, thankfully, happen way in the background and can be ignored), can usually tell in what direction it should head for the solution.

The advantages of the Excel Solver

Yes, Goal Seek and Solver are both iterative, but that doesn’t make them equal. In fact, Excel Solver brings a number of advantages to the table:

  • Excel Solver enables you to specify multiple adjustable cells.You can use up to 200 adjustable cells in all.
  • Excel Solver enables you to set up constraints on the adjustable cells.For example, you can tell Solver to find a solution that not only maximizes profit, but also satisfies certain conditions, such as achieving a gross margin between 20 and 30 percent, or keeping expenses less than $100,000. These conditions are said to beconstraintson the solution.
  • Excel Solver seeks not only a desired result (the “goal” in Goal Seek) but also the optimal one.For example, looking for an optimal result might mean that you can find a solution that’s the maximum or minimum possible.
  • For complex problems, Solver can generate multiple solutions. You can then save these different solutions under different scenarios.

When should you use Excel Solver?

Okay, let’s shoot straight here: Excel Solver is a powerful tool that most Excel users don’t need. It would be overkill, for example, to use Solver to compute net profit given fixed revenue and cost figures. Many problems, however, require nothing less than the Solver approach. These problems cover many different fields and situations, but they all have the following characteristics in common:

  • They have a singleobjective cell(also called thetarget cell) that contains a formula you want to maximize, minimize, or set to a specific value.This formula could be a calculation such as total transportation expenses or net profit.
  • The objective cell formula contains references to one or morevariable cells(also calledunknownsorchanging cells).Solver adjusts these cells to find the optimal solution for the objective cell formula. These variable cells might include items such as units sold, shipping costs, or advertising expenses.
  • Optionally, there are one or moreconstraint cellsthat must satisfy certain criteria.For example, you might require that advertising be less than 10 percent of total expenses, or that the discount to customers be an amount between 40 and 60 percent.

For example, the image below shows a worksheet data model that’s all set up for Excel Solver. The model shows revenue (price times units sold) and costs for two products, the profit produced by each products, and the total profit. The question to be answered here is this: How many units of each product must be sold to get a total profit of $0? This is known in business as abreak-even analysis.

That sounds like a straightforward Goal Seek task, but this model has a tricky aspect: the variable costs. Normally, the variable costs of a product are its unit cost times the number of units sold. If it costs $10 to produce product A, and you sell 10,000 units, the variable costs for that product are $100,000. However, in the real world, such costs are often mixed up among multiple products. For example, if you run a joint advertising campaign for two products, those costs are borne by both products. Therefore, this model assumes that the costs of one product are related to the units sold of the other. Here, for example, is the formula used to calculate the costs of the Inflatable Dartboard (cell B8):

In other words, the variable costs for the Inflatable Dartboard are reduced by one dollar for every unit sold of the Dog Polisher. The latter’s variable costs use a similar formula (in cell C8):

Having the variable costs related to multiple products puts this data model outside of what Goal Seek can do, but Solver is up to the challenge. Here are the special cells in the model that Solver will use:

  • The objective cell is C14; the total profit and the target solution for this formula is 0 (that is, the break-even point).
  • The changing cells are B4 and C4, which hold the number of units sold for each product.
  • For constraints, you might want to add that both the product profit cells (B12 and C12) should also be 0.

Loading the Excel Solver add-in

Anadd-inis software that adds one or more features to Excel. Installing add-ins gives you additional Excel features that aren’t available in the Ribbon by default. Bundled add-in software is included with Excel but isn’t automatically installed when you install Excel. Several add-ins come standard with Excel, including Solver, which enables you to solve optimization problems.

You install the bundled add-ins by using the Excel Options dialog box; you can find them in the Add-Ins section. After they’re installed, add-ins are available right away. They usually appear on a tab related to their function. For example, Solver appears on the Data tab.

Here are the steps to follow to load the Excel Solver add-in:

Define and solve a problem by using Solver

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Note: Versions of Solver prior to Excel 2007 referred to the objective cell as the "target cell," and the decision variable cells as "changing cells" or "adjustable cells". Many improvements were made to the Solver add-in for Excel 2010, so if you’re using Excel 2007 your experience will be slightly different.

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).

1. Variable cells

2. Constrained cell

3. Objective cell

After Solver runs, the new values are as follows.

On the Data tab, in the Analysis group, click Solver.

Note: If the Solver command or the Analysis group is not available, you need to activate the Solver add-in. See: How to activate the Solver add-in.

In the Set Objective box, enter a cell reference or name for the objective cell. The objective cell must contain a formula.

Do one of the following:

If you want the value of the objective cell to be as large as possible, click Max.

If you want the value of the objective cell to be as small as possible, click Min.

If you want the objective cell to be a certain value, click Value of, and then type the value in the box.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the non-adjacent references with commas. The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, enter any constraints that you want to apply by doing the following:

In the Solver Parameters dialog box, click Add.

In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

Click the relationship ( =, >=, int, bin, or dif ) that you want between the referenced cell and the constraint.If you click int, integer appears in the Constraint box. If you click bin, binary appears in the Constraint box. If you click dif, alldifferent appears in the Constraint box.

If you choose = for the relationship in the Constraint box, type a number, a cell reference or name, or a formula.

Do one of the following:

To accept the constraint and add another, click Add.

To accept the constraint and return to the Solver Parameters dialog box, click OK.
Note You can apply the int, bin, and dif relationships only in constraints on decision variable cells.

You can change or delete an existing constraint by doing the following:

In the Solver Parameters dialog box, click the constraint that you want to change or delete.

Click Change and then make your changes, or click Delete.

Click Solve and do one of the following:

To keep the solution values on the worksheet, in the Solver Results dialog box, click Keep Solver Solution.

To restore the original values before you clicked Solve, click Restore Original Values.

You can interrupt the solution process by pressing Esc. Excel recalculates the worksheet with the last values that are found for the decision variable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK. The report is created on a new worksheet in your workbook. If Solver doesn’t find a solution, only certain reports or no reports are available.

To save your decision variable cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

After you define a problem, click Options in the Solver Parameters dialog box.

In the Options dialog box, select the Show Iteration Results check box to see the values of each trial solution, and then click OK.

In the Solver Parameters dialog box, click Solve.

In the Show Trial Solution dialog box, do one of the following:

To stop the solution process and display the Solver Results dialog box, click Stop.

To continue the solution process and display the next trial solution, click Continue.

In the Solver Parameters dialog box, click Options.

Choose or enter values for any of the options on the All Methods, GRG Nonlinear, and Evolutionary tabs in the dialog box.

In the Solver Parameters dialog box, click Load/Save.

Enter a cell range for the model area, and click either Save or Load.

When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip: You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook. Each worksheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a worksheet by clicking Load/Save to save problems individually.

You can choose any of the following three algorithms or solving methods in the Solver Parameters dialog box:

Generalized Reduced Gradient (GRG) Nonlinear Use for problems that are smooth nonlinear.

LP Simplex Use for problems that are linear.

Evolutionary Use for problems that are non-smooth.

Important: You should enable the Solver add-in first. For more information, see Load the Solver add-in.

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell D5), until the total profit (objective cell D7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell D7, =SUM(Q1 Profit:Q2 Profit).

Variable cells

Constrained cell

Objective cell

After Solver runs, the new values are as follows.

In Excel 2016 for Mac: Click Data > Solver.

In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver.

In Set Objective, enter a cell reference or name for the objective cell.

Note: The objective cell must contain a formula.

Do one of the following:

Make the value of the objective cell as large as possible

Make the value of the objective cell as small as possible

Set the objective cell to a certain value

Click Value Of, and then type the value in the box.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the nonadjacent references with commas.

The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, add any constraints that you want to apply.

To add a constraint, follow these steps:

In the Solver Parameters dialog box, click Add.

In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

On the =, or >=, in the Constraint box, type a number, a cell reference or name, or a formula.

Note: You can only apply the int, bin, and dif relationships in constraints on decision variable cells.

Do one of the following:

Accept the constraint and add another

Accept the constraint and return to the Solver Parameters dialog box

Click Solve, and then do one of the following:

Keep the solution values on the sheet

Click Keep Solver Solution in the Solver Results dialog box.

Restore the original data

Click Restore Original Values.

To interrupt the solution process, press ESC . Excel recalculates the sheet with the last values that are found for the adjustable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK. The report is created on a new sheet in your workbook. If Solver doesn’t find a solution, the option to create a report is unavailable.

To save your adjusting cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

In Excel 2016 for Mac: Click Data > Solver.

In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver.

After you define a problem, in the Solver Parameters dialog box, click Options.

Select the Show Iteration Results check box to see the values of each trial solution, and then click OK.

In the Solver Parameters dialog box, click Solve.

In the Show Trial Solution dialog box, do one of the following:

Stop the solution process and display the Solver Results dialog box

Continue the solution process and display the next trial solution

In Excel 2016 for Mac: Click Data > Solver.

In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver.

Click Options, and then in the Options or Solver Options dialog box, choose one or more of the following options:

Set solution time and iterations

On the All Methods tab, under Solving Limits, in the Max Time (Seconds) box, type the number of seconds that you want to allow for the solution time. Then, in the Iterations box, type the maximum number of iterations that you want to allow.

Note: If the solution process reaches the maximum time or number of iterations before Solver finds a solution, Solver displays the Show Trial Solution dialog box.

Set the degree of precision

On the All Methods tab, in the Constraint Precision box, type the degree of precision that you want. The smaller the number, the higher the precision.

Set the degree of convergence

On the GRG Nonlinear or Evolutionary tab, in the Convergence box, type the amount of relative change that you want to allow in the last five iterations before Solver stops with a solution. The smaller the number, the less relative change is allowed.

In the Solver Parameters dialog box, click Solve or Close.

In Excel 2016 for Mac: Click Data > Solver.

In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver.

Click Load/Save, enter a cell range for the model area, and then click either Save or Load.

When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip: You can save the last selections in the Solver Parameters dialog box with a sheet by saving the workbook. Each sheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a sheet by clicking Load/Save to save problems individually.

In Excel 2016 for Mac: Click Data > Solver.

In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver.

On the Select a Solving Method pop-up menu, select one of the following:

GRG (Generalized Reduced Gradient) Nonlinear

The default choice, for models using most Excel functions other than IF, CHOOSE, LOOKUP and other “step” functions.

Use this method for linear programming problems. Your model should use SUM, SUMPRODUCT, + – and * in formulas that depend on the variable cells.

This method, based on genetic algorithms, is best when your model uses IF, CHOOSE, or LOOKUP with arguments that depend on the variable cells.

Note: Portions of the Solver program code are copyright 1990-2010 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Because add-in programs aren’t supported in Excel for the web, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions.

If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in.

More help on using Solver

For more detailed help on Solver contact:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Web site: http://www.solver.com
E-mail: [email protected]
Solver Help at www.solver.com.

Portions of the Solver program code are copyright 1990-2009 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Excel Solver – What Solver Can and Cannot Do

You are here

Solver’s basic purpose is to find asolution– that is, values for thedecision variablesin your model – that satisfies all of theconstraintsand maximizes or minimizes theobjectivecell value (if there is one). Thekindof solution you can expect, and how much computing time may be needed to find a solution, depends primarily on three characteristics of your model:

  1. Your model size (number of decision variables and constraints, total number of formulas)
  2. The mathematical relationships (e.g. linear or nonlinear) between the objective and constraints and the decision variables
  3. The use of integer constraints on variables in your model

Other issues, such aspoor scaling, can also affect solution time and quality, but the above characteristics affect theintrinsicsolvability of your model. Although faster algorithms and faster processors can help, somenon-convexor non-smooth models could take years or decades to solve to optimality on the fastest imaginable computers.

Your model’s total size and the use of integer constraints are both relatively easy to assess when you examine your model. The mathematical relationships, which are determined by the formulas in your model, may be harder to assess, but they often have a decisive impact on solution time and quality – as further explained starting with this topic.

  • If your objective and constraints arelinearfunctions of the decision variables, you can be confident of finding aglobally optimalsolution reasonably quickly, given the size of your model. This is alinear programmingproblem; it is also aconvexoptimization problem (since all linear functions are convex). The Simplex LP Solving method is designed for these problems.
  • If your objective and constraints aresmooth nonlinearfunctions of the decision variables, solution times will be longer. If the problem isconvex, you can be confident of finding aglobally optimalsolution, but if it isnon-convex, you can only expect alocally optimalsolution – and even this may be hard to find. The GRG Nonlinear Solving method is designed for these problems.
  • If your objective and constraints arenon-smoothandnon-convexfunctions of the decision variables (for example if you use IF, CHOOSE and LOOKUP functions whose arguments depend on decision variables), the best you can hope for is a “good” solution (better than the initial values of the variables), not a locally or globally optimal solution. The Evolutionary Solving method is designed for these problems.
  • You can use integer, binary, and alldifferent constraints on variables with all three Solving methods. However, these constraints make the problemnon-convexandmuchharder to solve.

With the Simplex LP Solving method, you can find a globally optimal solution given enough time – but you may have to settle for a solution that’s “close to optimal” found in a more reasonable amount of time. With the GRG Nonlinear and Evolutionary Solving methods, you should expect a “good,” but not provably optimal solution.

Excel File Repair Blog

Get Tips, Tricks and Fixes of MS Excel Issues

How to Fix Error Loading Excel Solver Add-In?

Excelis used for many purposes such as keeping a record of a large amount of data, analyzing, use of formula and others. Surprisingly Excel has its own solver know as Excel Solver.

Solver add-in is a Microsoft Excel add-in program that can quickly find simple problems such as values for a variable in objective functions and solves them easily. But sometimes when it is unable to solve any problem then it simply displays an error message-Error loading Excel solver add-inthat describes the issue.

If you are working in Excel you may encounter some issue loading the Excel solver add-in if you have WPS office installed, that is not fixed by the solver and it displaysExcel solver add-in not working. What would you do now? Need help.

Here in this article, you will get the workaround solution that will help you in SolvingMicrosoft excel solveradd-inissue. But before going straight towards the solutions first get to know what is an Excel solver add-in and how does it work?

What is Excel Solver?

Excel Solverhas its place in a special set of commands frequently stated to as What-if Analysis Tools. Its primary purpose is to simulate and optimize various business and manufacturing models.

TheExcel Solver add-inis exclusively useful for resolving linear programming problems and is sometimes called alinear programming solver. It can handle nonlinear problems as well.

Though Solver cannot crack every problem, it is really helpful in dealing with optimization problems.

For example, it can help you in making the best work agenda for the employees, maximize the return of investment, minimize the delivery costs, choose the finest budget for the advertising campaign, and so on.

Errors loading Excel Solver Add-In

You may experience some problems or errors loading Excel solver add-in. So, here is the solution, go through the below-provided methods to solve excel solver add-in not showing an issue.

Method 1: Check ActiveX Settings

Sometimes it happens that theActiveXsettings in Office Application are disabled and for that reason, it shows you excel solver add-in not working. To make it work then follow the below-given steps to check ActiveX settings:

  • OpenExcel
  • Click onFile>Options>Trust Centre
  • Click onTrust Centrebutton

  • Click on theActiveX settings
  • Now see IfDisable all controls without notificationare Ticked
  • Then choose“Prompt me”option
  • Click onOK

After doing the above steps excel solver-add in not working issue should be solved. If ActiveX settings are not disabled then you are required torepair Excel files.

Method 2: Enable Solver add-In

Try enabling solver add-in in order to fix Error loading Excel solver add-in problem. Follow the steps to do so:

  • OpenExcel
  • Click on theDeveloper tab

  • Now, click onExcel Add-ins
  • From the list of add-ins, selectSolver add-in
  • ClickOk

Apart from this, users are also encountering some errors in Excel solver and one of the common error isSOLVER.xlam.If you are facing ExcelSOLVER.xlamerror then below given are the ways to fix it.

How to Fix SOLVER.xlam error?

#1: Re-Enable Add-in

You may receive an error in ExcelSOLVER.xlamwhen trying to open MS Excel. To fix this error you need to re-enableSOLVER.xlamand to do so follow the steps given below:

  • OpenExcel
  • Go toFiles
  • Selectoptions>Add-insand selectGO

  • SelectSolver add-insand clickok

After enabling Solver add-ins re-open to excel and check whetherSOLVER.xlamerroris solved or not.

#2: Fix solver.xlam error

If you faceCannot run the macro SOLVER.XLAM!MainExerror message.” Try the following steps to fix this issue:

  • PressAlt+F11to open VBA
  • Click onTools> References>set toSOLVER

  • PressCtrl+Gto Show immediate code pane
  • Copy and paste:run “solver.xlam!auto_open”
  • Disable other Excel add-ins

Or Repair Office to fix the error if the above steps don’t work

Automatic Solution: MS Excel Repair Tool

If none of the methods stated above worked for you then to solve this Excel Solver issue you can try usingMS Repair Tool.

This Repairing tool allows you torepair damaged, corrupt, inaccessible Excel filealong with errors present in Excel and helps to make Excel work without ant error.

It not only recovers deleted or missing corrupt files but also recover charts, formula, spreadsheet & more. It is easy to use and support all Excel versions.

Comments: