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.
The project is initially set up as in Figure 2.
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.
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.
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:
@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.
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!
@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.
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.
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.
@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.
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.
Aubrey, Many thanks for sharing those valuable info with the community.
Vishal, Its true that I also did not know about this function.
Â
Â
Â
Shmicksha, Is it freely available or will have to buy?
Can you please share a link for that?
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
Samicksha, Many thanks for the link. It's really helpful.Â
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
@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.
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.Â
Got it yalanand, Many thanks for the guide lines.Â
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.