I always describe Solver as goal seek on steroids!! If you haven’t come across goals seek before then check out the link to my blog below. It is much simpler, but less flexible than Solver so a good place to start but if it isn’t able to do what you need then that’s where Solver comes in.
What is Solver?
Solver is used to maximise a value, minimise a value or hit a target value by changing one or more other cells. In addition constraints can be added if required. It is not available by default so has to be added in to utilise its functionality.
How to get Solver
- From the File tab select Options.
- Go to the Add-ins option on the left. Solver is an Excel Add-in which is what is currently selected so click on the Go button.
3. A list of Excel Add-ins is shown. Tick Solver Add-in and OK.
The solver icon will now be available at the end of the Data tab.
How to use Solver
For solver to work the cell that is being maximised, minimised or set to a value must be connected through calculations (directly or indirectly) to the cell or cells that are being changed.
- Click on the Solver icon on the Data tab.
- There are 3 main things to consider:
- Set Objective – This is the cell that is going to be set to max, min or a value of something.
- By Changing Variable Cells – This is the cell or cells that are going to be changed to meet the objective.
- Subject to the Constraints – These are other constraints that must be met.
3. The example above is maximising the figure in cell B15 which is the production profit for Q1 by changing the values in cells B11 to E11 which are the advertising spends for Q1, Q2, Q3 and Q4. If only these parts were completed then there is no constraint on how much spend we have in the advertising budget is to reach this solution. Adding the constraint does not let F11 (the total advertising spend) go above 40000.
4. More than one constraint can be added by repeatedly using the Add button on the right.
5. Solver could make some of the figures negative which does not make sense in most cases so ticking Make Unconstrained Variables Non-negative is often a good idea.
6. Click on the Solve button and the suggested results are shown in the cells.
7. The solver Results allows you to Keep the solver solution that has been produced or Restore the original values to set them back to what they were before.
8. If you are interested in how Excel got to the solution there are 3 reports that could help. These are Answer, Sensitivity and Limits. If selected then each of these reports are shown on extra sheet tabs.
9. Click OK.
NOTE: If you have added solver and you find you are not going to use it then it is sensible to remove the add-in. Otherwise, every time you open up Excel it will take longer. To remove it the same steps are followed as used to add it but Solver Add-in is unticked.