Advertisement

Blog

Using Solver in Excel, Part 1

Solver allows you to use an Excel spreadsheet to easily solve problems with multiple variables. We'll look at a couple of examples to see what Solver can do and how easy it is to use.

Solver is an add-in function in Excel and must be manually added. Here's how to do the install. In the help section (accessible via the F1 key or the help icon), enter solver as the search term. Depending on the Excel version, follow the link for “Quick start: Activate and use an add-in,” or “Load the solver add-in.” Then, just follow the instructions. While you're at it, select the Analysis ToolPak and Analysis ToolPak-VBA as well.

Some of the blogs that I wrote in the now defunct Microcontroller Central are pertinent to this and following blogs, so they've been copied over to the Planet Analog site.

Solver is a deceptively simple tool. It can help you solve multivariable problems. Let us go back to the 555 timer IC example that we used in Using Data Tables in Excel. Figure 1 is repeated here to simplify this discussion, but do go back and read that blog.

Figure 1

A timer IC, the 555, configured as an astable multivibrator with a frequency given by f = 1.44/((RA+2RB)•C). The mark to space ratio is given by RB/(RA+RB).

A timer IC, the 555, configured as an astable multivibrator with a frequency given by f = 1.44/((RA+2RB)•C). The mark to space ratio is given by RB/(RA+RB).

The project is initially set up as in Figure 2.

Figure 2

Initial set up with both the frequency and the mark/space values calculated.

Initial set up with both the frequency and the mark/space values calculated.

Click on the data tab of the Excel ribbon, and then click solver in the analysis subdivision. It will pop up a dialog as in Figure 3.

Figure 3

Setting up the solver parameters. Note the check mark next to Make unconstrained variables non-negative, so that you don't end up with negative resistor. Notice the Solver entry on the top right of the ribbon in the background.For a larger image, click here.

Setting up the solver parameters. Note the check mark next to Make unconstrained variables non-negative , so that you don't end up with negative resistor. Notice the Solver entry on the top right of the ribbon in the background.
For a larger image, click here.

You can only set one objective and that is to set the frequency (the target cell is in the set objective entry) to a value of 100,000. Notice you can choose a maximum or minimum setting as well. You then choose which variable cells can be changed. These do not need to be contiguous. You can select multiple ones either manually by separating with a comma or by holding down the Ctrl key while you make the different selections using the left button on the mouse.

Now we can see the versatility of the function, because we can get it to meet other criteria. Let's assume that we want the mark/space ratio to be between 0.45 and 0.5. Clicking on the add button allows you to set all manners of conditions. Click on solve and watch for a result. It may or may not solve the problem, and with the values above it will not. If you suspect that there is a solution, you can try a different solving method and play with the different options available to those methods. My experience though suggests that a review of the criteria and perhaps a judiciously chosen constraint will work wonders. In this case if we edit the second constraint to “>= 0.4” , we will return a result.

Figure 4

Solver results provides you with a number of options. If you select Restore original values the results will be nullified. Notice that you can save the results as a scenario.

Solver results provides you with a number of options. If you select Restore original values the results will be nullified. Notice that you can save the results as a scenario.

In part 2, we will continue our look at what Solver can do with an examination of the way in which it outputs data in reports .

Related posts:

16 comments on “Using Solver in Excel, Part 1

  1. RedDerek
    November 6, 2013

    @Aubrey – I look forward to part 2. I have used Solver a few times in the past, but always end up stumbling around. It is one part of Excel I would like to work with more. I will have to play with your example to get a better feel of the use.

  2. antedeluvian
    November 6, 2013

    RedDerek

     

    Thanks for the comment. I believe part 2 will be out tomorrow probably. I have added a bit of a puzzle at the end which can be easily solved by Solver- give it a shot!

  3. Vishal Prajapati
    November 7, 2013

    @Aubrey, I never knew such a function exists. It is interesting to see such functionality. I an really looking forward to your second part. I tried the first part and it turned out to be pretty well and the way you explained was easy enough to learn it. I loved it.

  4. Davidled
    November 7, 2013

    What version of excel did you use? I never see Solver Results tabs. That might save the design of analogy with report. I am using window XP. I wonder Solver could be updated by the excel program. Anyway, Generating the report is so much beneficial for engineer.

  5. antedeluvian
    November 7, 2013

    DaeJ

    What version of excel did you use? I never see Solver Results tabs.

    I used Solver as far back as Excel 97, and I think it predates that as well. The problem is that you need to install it. I describe how to do it for Excel 2010 in the 2nd paragraph of this blog. However the installation process has changed over the years. Back in the day (~2003) installation involved going to the Windows Control Panel and chooisng Add/Remove Programs . Select the Office entry and then choose the option to Change the installation. Then select the Add features, find the Solver in the Add-Ins sub-folder in the Microsoft Excel for Windows folder. Then click on Run from my computer and complete the installation directions. As I recall, you need the installation CDs at this point.

    Somewhere between 1997 and 2010 Microsoft changed the procedure and loaded the software with the installation and you only have to enable it as I describe in the blog. The best way to find out what to do in your case is press the help key (F1) when you are in Excel and search for Solver. The details should include installation instructions for your version.

  6. samicksha
    November 8, 2013

    @Daej: You can install the Solver add-in, if it is not already installed, you are right about reports, your objective and constraints are linear functions of the decision variables, you can be confident of finding a globally optimal solution reasonably quickly, given the size of your model.

  7. antedeluvian
    November 8, 2013

    DaeJ

     I never see Solver Results tabs.

    I'm sorry,  I believe I misread your post earlier. I though you were saying that you didn't have the Solver option in the ribbon/menu bar, but now that I re-read your comment I think you are asking about the dialog in Figure 4. The Solver Results dialog shows up once you have clicked on the Solve button in the Solver Parameters dialog (Figure 3) and the Solver has run to the point where it decides that it does or does not have a soltuion.

  8. PCR
    November 10, 2013

    Aubrey, Many thanks for sharing those valuable info with the community.

    Vishal, Its true that I also did not know about this function.

     

     

  9. PCR
    November 10, 2013

     

    Shmicksha, Is it freely available or will have to buy?

    Can you please share a link for that?

  10. samicksha
    November 13, 2013

    Not sure though, but check out this link, i hope it will help you..

    http://office.microsoft.com/en-in/excel-help/load-the-solver-add-in-HP010021570.aspx

  11. PCR
    November 14, 2013

    Samicksha, Many thanks for the link. It's really helpful. 

  12. onthejob
    November 14, 2013

    Hi,

    I've used the Excel solver occasionally for many years.  Most recently I used it to match the I-V model parameters of a power diode in a design I had been asked to analyze (it quickly picked Is and Rs based on datasheet I-V info I put into Excel). I could have done this particular simple task “by trial and error”, but using the solver was so easy it was a no-brainer.  I've also successfully done considerably more complex (many parameters) curve fits using it.

    It's a really useful tool but few people know it even exists because you have to know how to install it and use it.  Not difficult though!  Thanks for helping introduce people to this.

    R. Steve Scott

  13. yalanand
    November 30, 2013

    @Kagan, thanks for the post. I wasn't aware of this feature before and hence I don't know much about solver but after reading this blog I am sure I will be using this feature to solve equations.

  14. yalanand
    November 30, 2013

    Shmicksha, Is it freely available or will have to buy?

    @Ranasinghe, I think its freely available. If you want slightly more powerful solvers you can also use opensolver. OpenSolver is an Excel VBA add-in that extends Excel's built-in Solver with a more powerful Linear Programming solver. 

  15. PCR
    November 30, 2013

    Got it yalanand, Many thanks for the guide lines. 

  16. SunitaT
    November 30, 2013

    Solver is ration of a suite of directions occasionally called what-if investigation tools. With Solver we could find an ideal value for a formulation in one cell called the detached cell, subject to restraints, or limits, on the standards of other formulation cells on a work sheet.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.