ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use the Excel 2007 and Excel 2010 Solver Tool in a spreadsheet for solving problems and optimization

Updated on March 27, 2013

Introduction

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:

  1. 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
  2. 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
  3. 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.

Data to be used by the Solver tool in Excel 2007 and Excel 2010 and the results of the what-if analysis.
Data to be used by the Solver tool in Excel 2007 and Excel 2010 and the results of the what-if analysis. | Source

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

Adding the Solver tool to Excel 2007.
Adding the Solver tool to Excel 2007. | Source
  • 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.

How to add the Solver Tool to Excel 2010.
How to add the Solver Tool to Excel 2010. | Source

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)

Adding the Developer tab to Excel 2010.
Adding the Developer tab to Excel 2010. | Source

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)


Configuring the Solver tool parameters for what-if analysis in Excel 2007 and Excel 2010.
Configuring the Solver tool parameters for what-if analysis in Excel 2007 and Excel 2010. | Source
  • 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

Configuring the Solver Results for your what-if analysis in Excel 2007, including selecting the appropriate reports.
Configuring the Solver Results for your what-if analysis in Excel 2007, including selecting the appropriate reports. | Source
  • 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

The report generated by Excel 2007 showing the results of the what-if analysis.
The report generated by Excel 2007 showing the results of the what-if analysis. | Source

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)

Configuring a constraint in your what-if analysis performed by Excel 2007 and Excel 2010.
Configuring a constraint in your what-if analysis performed by Excel 2007 and Excel 2010. | Source

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.

The report generated by Excel 2007 or Excel 2010 showing the results of the what-if analysis with a constraint added.
The report generated by Excel 2007 or Excel 2010 showing the results of the what-if analysis with a constraint added. | Source

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

Illustration of the Solver tool with a what-if analysis with no solution.
Illustration of the Solver tool with a what-if analysis with no solution. | Source

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.

Conclusion

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.


What do you plan to use the Solver Tool for?

See results

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)