In the first part of this blog, Using Solver in Excel, Part 1, we started looking at what we can do with the Solver add-in to the Excel spreadsheet program. We looked at using Solver for the design of a 555 timer circuit. We will finish that problem, briefly consider a Solver shortcoming, and then look at one more problem that is a bit of a riddle.
You will notice that when the solver has completed its run, if you select one or more of the options in the Reports window, the associated number of tabs are added to the workbook. There is the option of a summary of the results, information of the sensitivity of the results to variable change, and the limits of the values that have been used.
One of the limitations of this approach is that it finds only one solution and not necessarily the optimal solution. Even if you use the scenarios feature in conjunction with the solver, you have to find some constraint to adjust to arrive at what you consider optimal. But there are also benefits with the Solver function and it is that the settings are preserved so that you don't have to set them up every time you want to make a small modification. You can, of course, create a VBA program to optimise the whole process.
The holy grail of using Excel to solve electronics problems is to use it to find the nearest E91 resistor values. The most common approach is a lookup table. In finding a value for a resistor or capacitor, we are limited to discrete values; but Solver takes the approach that the input values are continuous. It would be nice if you could set up one of the input values as an integer and then use this as an index to return a value from a lookup table.
One of the shortcomings of Solver, and I believe it is a major one, is that it does not allow the inputs to be limited to integers and so you cannot achieve this level of versatility. The funny thing is that when I first investigated this I was working with Quattro Pro (is it still around?) and its Solver function could do this. Both Excel and Quattro Pro use the same engine from Frontline Solvers, but obviously with different capabilities.
Way back in July 2013, Max (“the Magnificent”) Maxfield posed a problem on All Programmable Planet that is deceptively complex. Since APP is no more and there is no blog repository I have reproduced the problem here with Max's kind permission.
What we have here is an alleyway bordered by two brick two walls that are “x” feet apart. Starting from the lower left-hand corner of the alleyway, we have a ladder that's 24 feet long and whose upper end is leaning against the right-hand wall. Meanwhile, starting from the lower right-hand corner of the alleyway, we find another ladder that's 36-feet long and whose upper end is resting against the left-hand wall.
As seen from a position looking down the alleyway, the perpendicular height from the floor to where the ladders appear to cross is 10 feet. The problem is to come up with a simple mathematical formula to determine the value of “x” — that is, the width of the alley.
The discussion is now lost, but it results in two simultaneous equations that are intricately linked through square roots. Common consensus in the responses was that it is rather time consuming to solve with traditional methods. Using the brute force approach of Solver (changing values until something works) it became so easy that the solution is elegant in its brevity. See if you can get it to work. I will provide my solution if anyone wants it in about a week.
Have you used Solver in any way? Did it provide the answers you'd hoped for?