How to use the Excel 2007 and Excel 2010 Solver Tool in a spreadsheet for solving problems and optimization
Welcome to my latest hub on Excel 2007 and Excel 2010. Today, I will be looking at using the Solver tool. The Solver tool enables you to calculate how you could achieve different goals or forecasts or how you could optimize your data to achieve a specific result. By changing other values, Solver can also calculate the maximum or minimum value of the value you are interested in. You can use this tool to:
- Solve problems – for example, calculating the number of daily hits my websites would need to achieve to reach an average of 100 hits on a Monday
- To perform what-if analysis –as an example, you could alter the total value of your marketing budget and see the effect it has on your projected profit
- To perform optimization on your data by creating optimization models – you can use Solver to optimize a particular variable such as profit by creating an optimization model. For example, how could I optimize my pension fund by allocating my portfolio amongst a number of investment options?
In today’s hub, I will use the following data table as my example:
Using the Solver tool, I want to find out how many hits my websites would need to reach an average of 100 hits on a Monday. The data shows the average number of hits my hubs receive broken down by day of the week as well as an overall weekly average.
Adding the Solver tool to Excel 2007
The Solver tool button sits on the Data tab, in the Analysis group. If you do not have the Solver button:
- Click on the Excel button
- Select Excel Options
- Next, select the Add-Ins tab
- Down towards the bottom of that dialogue box, select Go
- If Solver Add-in is not selected, then select it
- If it is not present, then Browse to it to add it (browse to \Office12\Library)
If it is present, but not showing in Excel:
- First deselect it,
- Then add it back in (this is a common problem in Excel 2007 where add-ins have been added but their buttons are not visible in the ribbon)
Adding the Solver tool to Excel 2010
If you are using Excel 2010, to add the Solver Tool button:
- Click on the Developer tab
- Select the Add-Ins button which is in the Add-Ins group and select the Solver Add-in as below and click OK.
If you don’t have the Developer tab added to your Excel 2010:
- Browse to the File menu
- Select Options
- Choose the Customise Ribbon tab on the left hand side
- Select Developer in the Main Tabs section (illustrated below with the red arrow)
Using the Solver tool in Excel 2007 and Excel 2010 to perform What-If or Problem Analysis
Now that we have our goal (an average of 100 hits on a Monday) for the what-if or problem analysis, we need to configure the parameters for the analysis. To begin we need to access the Solver tool. It can be found on the Data tab, in the Analysis group:
- To begin, click the Solver button
- After clicking on the Solver button, the Solver Parameters dialogue box opens
- In Set Target Cell, select the cell that contains formula you are performing the analysis on (in my case the average number of hits on a Monday or cell B25)
- For Equal To, in this case I set it to Value of 100
- The range in By Changing Cells is the range of cells that contains the data for the hits on a Monday (B4 to B24)
- Click Solve
- The Solver Results dialogue box will now appear
- Select Restore Original Values or the Solver tool will overwrite your original data and you will not be able to Undo
- In the Reports section, select Answer and then click OK
- Excel rolls back your data to the original values (the eagle eyed amongst you may have noticed that before clicking OK, Excel had already updated your original data with the new what-if analysis data)
- In addition, Excel creates a new tab in your worksheet and creates a report for you.
The figure below shows the report I received for my what-if analysis
Now, you will notice that although Excel 2007 or Excel 2010 has performed exactly what I wanted for my what-if analysis, there is a problem here.
For all the days except the first day, Excel has completed the analysis but hasn’t used whole numbers. So for example, in $B$5, Excel has adjusted 39 to 90.6. As .6 of a hit is not possible, I will need to tell Excel to only use whole numbers.
To achieve this, I need to a constraint to the Solver tool.
- Once again, I click the Solver button
- Excel 2007 has very kindly remembered my last what-if analysis so I only need to click on Add to add a constraint
- Under Cell Reference, I select the range that contains all my Monday data (B4 – B25)
- Select int in the middle drop down box
- Excel 2007 and Excel 2010 will change the Constraint to an integer (whole number)
As before, remember to select Restore Original Values and choose Answer in the Reports section
This time, Excel 2007 or Excel 2010 has performed the analysis and each adjusted cell has a whole number which is much more useful. You can also see that Excel has also listed the constraints placed upon it.
Note: the constraints list all of the cells; there was insufficient space in the figure to display them all.
How to determine if Solver cannot find a solution to my problem or optimisation in Excel 2007 and Excel 2010
If you give Excel 2007 or Excel 2010 a what-if scenario that cannot be solved (for example if I add another constraint that no number in my data range can be above 50 but I am looking for an average equal to 100), Excel will not give you an immediate error to advise you that what you are asking it to do is impossible. Instead, when you click Solve, the Solver Results dialogue box will state that Solver could not find a feasible solution and all the reports will be greyed out as below
Select Restore Original Values, click OK and then go back to the Solver and change your constraints to allow Excel 2007 to find a solution for your what-if analysis.
The solver tool is a powerful tool that is used for problem solving, optimisation and what-if analysis on your data to allow you to create forecasts from your data and also test specific goals.
To ensure that Excel 2007 or Excel 2010 does exactly what you want it to do; you can also add constraints to the analysis. In the example I used, I wanted to see what hits my websites would need to achieve to give me an average of 100 on Mondays, but I wanted Excel to only return whole numbers (a daily total of 35.6 hits is not very useful).
In some instances, Excel 2007 is unable to provide a solution to your what-if analysis and that is also discussed here in this hub. I also discussed adding the Solver add-in should it not be loaded by default.
I hope that you enjoyed reading this hub as much as I enjoyed writing it. As with many tools in Excel 2007, the usage of this tool is limited only by your imagination and the uses you can find for it. Please feel free to leave any comments you may have below.