# 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).

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.

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.

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.

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.

9. PCR
November 10, 2013

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

10. samicksha
November 13, 2013

11. PCR
November 14, 2013

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.

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