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 .