Solver Add-in

This is an additional add-in that can be loaded when necessary. It is not installed by default.
The Solver add-in allows you to perform "what if" calculations based on several variables.
This add-in can be used to solve formulas that contain several variables and is an extension to the built-in Goal Seek (one variable) functionality.
The Goal Seek command can be used for finding simple target values but this add-in can be used for more complex problems
This add-in also allows you to provide one or more constraints (*i.e. conditions) that must be met for the solution to be valid.
This add-in has several uses, one of which is to solve both linear equations.
It is important to remember that finding a solution to solve your formulas is not always possible.


Is it Installed ?

Data Tab, Analysis group, Solver

alt text

Differences between Solver and Goal Seek

1) Solver can solve formulas (or equations) which use several variables whereas Goal Seek can only be used with a single variable.
2) Solver will allow you to vary the values in up to 200 cells whereas Goal Seek only allows you to vary the value in one cell.
3) It is possible to save one (or more) models with Solver.
4) Solver allows you to add constraints that must be true for the solution to be valid.
5) Solver can be used to find the value of the variables that give a formula a maximum or minimum value as well as a specifc value.


New Features in 2010

Three different solving methods: Simplex method, GRG Nonlinear and Evolutionary
Three new reports have been added: Linearity, Feasibility and Population


Add-in Location

Excel 365 - C:\Program Files\Microsoft Office\root\Office16\Library\SOLVER.XLAM | SOLVER32.DLL
Excel 2021 - C:\Program Files\Microsoft Office\Office16\Library\Solver\SOLVER.XLAM | SOLVER32.DLL
Excel 2019 - C:\Program Files\Microsoft Office\Office15\Library\Solver\SOLVER.XLAM | SOLVER32.DLL
Excel 2016 - C:\Program Files\Microsoft Office\root\Office16\Library\SOLVER.XLAM | SOLVER32.DLL


Solver Add-in Dialog Box

alt text

Set Target as - Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.
Equal To - Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.
By Changing Cells - Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.
Guess - Guesses all nonformula cells referred to by the formula in the Set Target Cell box, and places their references in the By Changing Cells box.
Subject to the Constraints - Lists the current restrictions on the problem.
Add - Displays the Add Constraint dialog box.
Change - Displays the Change Constraint dialog box.
Delete - Removes the selected constraint.
Solve - Starts the solution process for the defined problem.
Close - Closes the dialog box without solving the problem. Retains any changes you made by using the Options, Add, Change, or Delete buttons.
Options - Displays the Solver Options dialog box, where you can load and save problem models and control advanced features of the solution process.
Reset All - Clears the current problem settings, and resets all settings to their original values.


Using the Add-in

SS - solver parameters dialog
Provide 4 items of information
1) Objective
2) Target Cell - Solver adjusts the values based on this formula.
set target cell (F9) mimising the total expenditure
3) Variables / Adjustable Cells - Solver changes these cells to new values.
the number of ads in each publication (E3: E8)
4) Constraints - Solver adjusts the values according to these conditions.
total expenditure must be less that the budget available (F8 <= G13)


Define the target cell and its value
Add / Specify the constraints
SS - Add constraint



SS - Solver results
Keep Solver Solution -
Restore Original Value -



Important

If you want to limits the changes that Excel can make to a cell value or a target value you can add a constraint.
This can also be used to find combinations of variables that may provide a maximum or minimum result.
This add-in is licensed to Microsoft by Frontline Systems Inc. (solver.com) and you can obtain more information about this add-in from their web site.
You only need to add constraints to cells which are going to be changed.
This add-in is extremely useful for finding the roots of equations.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext