Excel has several tools for solving complex equations like those that often arise in circuit design. In this blog, I will deal with the simplest, called “Goal Seek.”
The very basis of Excel is that you can enter a formula in a cell and the program will automatically calculate the answer. However, in Excel's fundamental form of equations you have to be able to express the variable you are trying to calculate as “x=…”, where the desired variable is isolated on the left-hand side of the equation and all the other variables and constants are on the right-hand side. Frequently, deriving this form of the equation can be difficult, if not impossible.
Happily, Excel has several other tools available for solving equations. The one I would like to discuss in detail here is called “Goal Seek.” In principle, Goal Seek slowly increases the value of one cell in the worksheet until the value of another cell meets a conditional requirement. Here's an example — I developed this with Microsoft Excel 2010, although the only difference between it and other versions seems to be the ribbon interface (let’s not go there).
Figure 1 shows the classical set up for an LM317 adjustable voltage regulator. The output voltage is given by the equation Vout =1.25(1+(R2/R1)) + (Iadj *R2). Typically, we know the output voltage we desire and we want to calculate the resistor values. National Semiconductor originally suggested 240Ω for R1, so let us fix this at 243Ω and the equation reduces to one unknown. It's true that with some manipulation, we can extract R2 so that it's isolated on the left-hand side, but that is tedious. And anyway, this is an example; there will be other situations where you can’t.
Let’s set up the worksheet as in Figure 2. Cell B5 is entered numerically and is chosen to be the worst-case Iadj taken from the data sheet. R1 is the fixed resistor value, as we discussed earlier, and B6 is a simple value as a placeholder to be used in the calculation. Note the formula for Vout in cell B4 as shown in the formula bar just above the actual worksheet, which lets me express Iadj in µA.
Now, click on the Goal Seek option as shown in Figure 3, noting the ribbon selections of the Data tab followed by What-If Analysis . This will pop up the dialog as shown in Figure 4.
The Set cell is the cell that is to be measured and changed to the value desired in the next entry. Note that you don’t actually have to type in the $B$4 entry, just make sure the text cursor is in that field of the dialog box (as if you were going to type a value in), and then click on the spreadsheet cell that you want. The correct cell reference will show up as the entry. (As an aside, just in case you don’t know, the $ sign fixes the column or the row to a non-changeable value. When you copy a formula in Excel it will adjust the references accordingly — a powerful tool, but sometimes you don’t want anything to change, but instead, to always reference the same column and/or row. The “$” sign prevents change. This notation goes all the way back to VisiCalc.)
The result is shown in Figure 5. There are techniques where you can fetch the nearest standard resistor value to be used for R2, but we will get to those some other time (or buy my book!).
Goal Seek is really basic and it doesn’t even allow using a cell as the input for the “To value” entry, nor does it allow a conditional expression like “>10.” It certainly doesn’t give you the chance to play with different values of another variable like R1 to find a better value for R2. Still, this will give you a taste of the Excel features that I will describe in future blogs.
Do you think you will be able to use this feature?