THE MAGAZINE FOR COMPUTER APPLICATIONS
Circuit Cellar Online offers articles illustrating creative solutions
and unique applications through complete projects, practical
tutorials, and useful design techniques.

DO YOU EXCEL IN ELECTRONICS?

Part 3: The Bottom Line
by Aubrey Kagan

Start ı Calculating The Odds ı Strange Occurrences ı Can You Relate? ı Nothing Up My Sleeve ı Source and PDF

This month, I conclude this series by looking at Excelıs Solver function. This function allows complex models to be solved with several variables. It provides a powerful tool to solve problems where the variables may be interdependent.

For good measure (although unrelated), I have included a description on the use of regression. Most Excel texts describe regression as the linear relationship between an output and several different inputs. Iıll show you how to look at both linear and quadratic relationships and how the technique can be extended.

Goal Seek, which I explained in Part 2 (Circuit Cellar Online, February 2002), is fine for problems in which there is only one variable or at least when all of the degrees of freedom can be fixed leaving only one variable. In the LM317 example I used last month, I fixed one of the resistors to 243 ohm based on the suggestion from the datasheet. This kind of educated guess is not always possible if you donıt have enough information or if the relationship is tricky.

Excel provides another approach called Solver. It too, is an iterative approach that changes multiple values and checks the response of the calculated answer. As with Spice, convergence can be an issue. The Solver function uses a number of mathematical approaches to arrive at an answer, but the best way to arrive at a logically acceptable solution is to impose constraints on the problem. Excel allows a number of different mathematical approaches, but typically if there is no convergence, it is better resolved by a well-chosen constraint than by changing the mathematical approach.

Once again, as with Spice, the better you understand your application, the greater the chances of succeeding with the calculation. The initial values you place in a spreadsheet act as a seed for the solver. Because there are many possible solutions to a problem, Solver has to start somewhere, and it takes these values as the starting point. It is possible to have many different solutions and to save these scenarios, modify something, rerun Solver, and compare the results.

NEXT