Advertisement

Blog

Using Solver in Excel, Part 2

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?

Related posts:

18 comments on “Using Solver in Excel, Part 2

  1. eafpres
    November 8, 2013

    Hi Aubrey–I use solver from time to time.  I had a situation that required finding a triangular distribution, where the distribution represented possible vales of a forecast, and the givens were the most likely (aka the expected) value and the low and high boundaries of a 90% confidence interval.  It turns out, much like your example, that you can use plane geometry to get equations but they don't lend themselves to closed form algebraic solution.  I used solver to find the upper (or lower) bound of the distribution.

    Issues I found were that in some cases it was sensitive to the initial guess, and it can generate “solutions” which are nonsense.  I like that you can put constraints on pretty much any value used in the calculation to avoid non-physical or impossible values (like negative lengths).  However, I would encourage anyone using it to check the results back in your formula to ensure it makes sense.

  2. antedeluvian
    November 8, 2013

    eafpres

     I like that you can put constraints on pretty much any value used in the calculation to avoid non-physical or impossible values (like negative lengths).  However, I would encourage anyone using it to check the results back in your formula to ensure it makes sense.

    Well said! I couldn't agree more.

    If you do pick up conflicts within the results , or even a failure to compute I just want to reiterate what I said in Part 1-

    My experience though suggests that a review of the criteria and perhaps a judiciously chosen constraint will work wonders

  3. antedeluvian
    November 11, 2013

    Here is how I set the problem up using Excel/Solver. First I decided to describe the situation as two straight lines on a Cartesian graph with the origin at the lower left. I designated the alley width as XP and the result was in cell B2- pick any value to start off with.  Using Pythagoras, the 36' ladder has a slope M1 of = -1*SQRT(36^2-B2^2)/B2 (the formula in cell B4) and an intercept K1 on the y-axis of =SQRT(36^2-B2^2) (the formula in cell B5). The slope of the 24' ladder M2 is =SQRT(24^2-B2^2)/B2 (the formula in B10). Where the ladders cross the two line equations are equal. “x” is the independent variable and is represented by the value in cell B7- choose any value. For the 36' ladder the “y” value is given by =B4*B7+B5 (the formula in cell B8). The ''y” value for the 24' ladder is =B10*B7 (the formula in cell B11).

    Then initiate up Solver. This figure  shows how I set it up. (Please note I originally named the cells, so in the variable cells entry “xx” refers to cell B7 and “XP” refers to cell B2. You could have used the cell reference instead of the names. If there is demand I could do a blog on naming cells.)

    We know from the problem that where the two ladders intersect the height is 10, so I am setting the “y” for the 36' ladder to 10' as the objective. Solver is instructed to change the values in cells B7 and B2 until the objective reaches 10'. This may find a result that is not correct (as described by eafpres earlier), so I added a constraint that the “y” value of the 24' ladder must also be 10'. The result shows up in cell B2 (18.86')  

  4. TomC123123
    November 11, 2013

    In case you are curious / interested:

    This can be solved by one of two methods:

    1.  Solution of simultaneous two non-linear equations which can be solved by Newtons method.

    2.  A real root of a 4th order polynomial.

    (This exercise was posed to us years ago in a Numerical Methods course by Dr. Arbic at LSSU.)

    TomC

  5. eafpres
    November 12, 2013

    Working on another problem a couple years ago, I used an Excel Macro called RPolyJT, which finds roots of polynomials.  I had a situation where I needed roots of a 7th order polynomial and it worked quite well.  It contained this note:

    Original Fortran 77 source code from: http://www.netlib.org/toms/493
    Converted to VBA and modified by Doug Jenkins 11 Jan 2011

    You can find a lot of interesting Excel material from Mr. Jenkins here:

    http://newtonexcelbach.wordpress.com/2011/01/17/evaluating-higher-order-polynomials/

  6. antedeluvian
    November 13, 2013

    eafpres

    You can find a lot of interesting Excel material from Mr. Jenkins here:

    This looks like a great resource. Thanks for the pointer.

  7. eafpres
    November 13, 2013

    You have to respect someone who is repurposing Fortran in the 21st century. It would be interesting to know how many PA readers still have some Fortran connection. Kind of makes one want to reminisce about the good old days, doesn't it?

  8. estearg
    November 13, 2013

    There is also a solver in LibreOffice. Although the default is a linear solver, there is an extension for non-linear programming.

    Good article. Draws attention to a tool usually overlooked and very useful.

  9. Scott Elder
    November 13, 2013

    I've certainly used Excel many times in the past, but I think every engineer should have a copy of SCILAB and XCOS (both free) on their computer as well.  These are tools with substantial equivalence to MATLAB and SIMULINK, tools that cost nearly $10,000.

    http://www.scilab.org

     

  10. SunitaT
    November 30, 2013

    These are tools with substantial equivalence to MATLAB and SIMULINK, tools that cost nearly $10,000.

    @Scott, thanks for pointing to these tools. I was using Octave instead of Matlab and I was not aware of any tool which was equivalent to SIMULINK. I will definitely try to get copy of SCILAB and XCOS. 

  11. SunitaT
    November 30, 2013

    There is also a solver in LibreOffice. Although the default is a linear solver, there is an extension for non-linear programming.

    @elucches, true. I wonder why they have differentiated between linear and non-linear programming. What is the difficulty in combining both of them and releasing it as a single extension ?

  12. SunitaT
    November 30, 2013

    You can find a lot of interesting Excel material from Mr. Jenkins here:

    @eafpres, thanks a lot for sharing the link. I think VBA is a very powerful programming language and we can use that langugage along with excel features to model complex systems.

  13. SunitaT
    November 30, 2013

    Issues I found were that in some cases it was sensitive to the initial guess, and it can generate “solutions” which are nonsense. 

    @eafpres, thanks for sharing your experience. How to make sure that the value of  initial guess is proper and is generating proper solutions ? Does it mean that we should have an idea what the output  number looks like ?

  14. Victor Lorenzo
    November 30, 2013

    @Scott, “I think every engineer should have a copy of SCILAB “, and also a 'scope, logic analyzer, programmable power supply… and a warehouse to gather them alltogether ;).

    I used MathLab sometime ago for analyzing EEG, ECG and EMG signals, but at present I use SCILab for analyzing RFID signals (quite a change in signals type). It's a great tool despite it is slow in loading large (16MB+) CSV files.

  15. SunitaT
    November 30, 2013

    I used MathLab sometime ago for analyzing EEG, ECG and EMG signals, but at present I use SCILab for analyzing RFID signals (quite a change in signals type).

    @Victor, I am also regular user of matlab. I would like to know how easy or difficult is it to work on SCILab ? Is the interface something similar to Matlab ?

  16. Victor Lorenzo
    December 1, 2013

    Yes @SunitaT, SCiLab is very similar to MatLab and uses one programming language which is compatible with the one used by matlab. The installation is just click-and-go and the build-in editor integrates directly with the execution engine. Just a few things present some incompatibilities with matlab, but there're plenty of documents and tutorials.

    The zoom-in/out and paning in the graphs view is not too confortable, but you can get used to it.

    I was using a very dissimilar PC when working with matlab and now, so in terms of performance perhaps someone else could give us his(her) oppinion.

  17. amrutah
    December 1, 2013

    I have used SCILAB and SCICOS for modelling and simulating PLL.  It is much similar to MatLab, but since it is a opensource there were issues like machine not responding, memory hogging.  When it comes to using the command-line interface SCILAB has very much the same syntax as MatLab.

  18. eafpres
    December 2, 2013

    Hi Sunita–unfortunately the situation is exactly as you stated–you need some idea of what the result will be to provide good initial values.  This implies the solver is not a black box, but just a tool and you must use it intelligently.

Leave a Reply

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