Up to this point the discussions have been about computer implementation of inductance calculations that are analytically derived and where the formula can be neatly arranged so that the unknown variable appears by itself on one side of the equation. Hence it can be solved by plugging in the known values and solving the equation. Unfortunately it's not always possible to arrange equations so neatly. In that case, we can resort to a trial and error approach. We can enter the various formulae onto a spreadsheet and adjust the unknown values (hopefully using an organized method) until the numbers work out. Sometimes there is only one unknown value that needs to be worked out and sometimes there are many.
An example of this situation is given in Section 3 of the Bandspreading discussion. In sections 1 & 2, formulae were developed whereby the variable capacitor range and the upper and lower frequency limits could be specified, and the remaining components could then be directly calculated. In section 3, it was not feasible to derive similar direct formulae for the component values. Hence, the practical solution was to enter the frequency formula into a spreadsheet and adjust values of coil inductance, tap position, and bandset capacitance until the upper and lower frequency values were as required.
Over the years Solver routines have been available for spreadsheet programs either built-in or as add-ons. In my own experience with Open Office Calc, both the Solver and Goal Seek functions have changed significantly from one version to the next, such that it has been impossible to rely on them to work consistently. As a result I've coded a solver as an Open Office Basic Macro which is versatile enough to solve many different problems. The solver uses the Nelder-Mead "Downhill Simplex" method. What the solver routine does, in essence, is adjust the values of one or more input variables to a function, in order to minimize the value of a single output variable. I won't go into the details here of how it works, because there are many resources on the internet which describe it in great detail. A good explanation can be found on Wikipedia. I won't go into the details of how the routine was coded either. While it's somewhat involved, it is a straightforward coding of the general Nelder-Mead algorithm, and the Wikipedia article has links to Nelder-Mead computer source code. More important is what we can do with the solver. Several examples follow. One comment before getting into the examples though: The title of this section is "Solvers and Optimizers" and so far there has been no mention of Optimizers. That's because the Solvers and Optimizers being discussed here, are the same thing. "Solver" is a term that has been popularized in spreadsheet programs, while "Optimizer" has been the traditional term used in computing technical literature.
Example - Tapped Coil Bandspreading
In Section 3 of the Bandspreading discussion, a formula was developed for the resonant frequency of a tank circuit which has a "bandset" capacitor CA across the entire coil, and another capacitor CB connected to a tap on the coil. We can enter arbitrary component values and directly calculate the resonant frequency, but if we want to design a tank circuit with a specified variable capacitor and specified upper and lower frequency limits, it's not feasible to rearrange the formula to give us the required component values. Instead we enter all of the parameters into a spreadsheet along with the resonance formula, and then adjust parameters until we get the frequency limits we want. We will assume that we have been given a bandspread variable capacitor CB with a fixed range which we cannot change. The only variables which we can change are the coil inductance, the coupling coefficient between the two parts of the coil, the bandset capacitor CA, and the tap position on the coil to which the bandspread variable capacitor is connected.
We can have the solver adjust these variables, but as mentioned above, the solver monitors a single output variable which it tries to minimize. In our example problem we have two output variables (upper and lower frequencies) that we need to monitor, and we don't want to minimize them; we want them to get as close to the specified design target frequencies as possible. However, with a bit of thought, we can create an error function which will meet the necessary criteria. First, let us define some variables:
fHT - Desired (Target) upper frequency limit of the tuned circuit
fLT - Desired (Target) lower frequency limit of the tuned circuit
fH - Actual upper frequency limit of the tuned circuit
fL - Actual lower frequency limit of the tuned circuit
We will have a useable solution to our problem when
fHT = fH and fLT = fL
Until we reach this condition, we need some way to determine how close we are to the solution. The error in the upper frequency can be defined as
ErrH = | fHT − fH |
and the error in the lower frequency can be defined as
ErrL = | fLT − fL |
where the vertical bars indicate that we are taking the absolute value. We want the error to be a positive number at all times. We can then create an expression for the overall error as:
Err = | fLT − fL | + | fHT − fH |
It can be seen that Err will be equal to zero only when both the upper and lower frequency limits are exactly on target. The reason for using the absolute values will now become clear. If we didn't use absolute values, it's possible we could have a situation where ErrH is positive and ErrL is negative, and by adding them together they could cancel each other out and incorrectly produce a zero error value, which would give a false indication that a viable solution has been found. Hence, we ensure that the individual error values are always positive so that cancellation cannot occur. While we can use the ABS() function in the spreadsheet to get positive numbers, it is generally preferable to take the square of the differences like this:
Err = ( fLT − fL )2 + ( fHT − fH )2
Squaring the differences always gives positive numbers, and in addition, it also gives a smoothly varying error function which is preferable to the discontinuous function which we would get from using the ABS() function. There are also statistical reasons for using the square of the error, but I won't get into that discussion here.
This example problem has been entered into an Open Office Calc spreadsheet which can be downloaded here. (This has been tested in Open Office version 3.)
When you open this spreadsheet file, Open Office will warn you that it contains macros, and will give you the option to disable them. The reason for the warning is that it is possible to create malware macros which can harm your computer, either by destroying files on your hard drive, or by accessing the Internet to do other malicious things. Therefore, it's wise to disable the macros the first time you open any such file, then once the file is open, go into the macro editor and examine the code to satisfy yourself that there are no commands which write to, or erase files; or access the internet. Once you are satisfied, close the file and reopen it with the macros enabled.
For this example, we want to tune from 5500 kHz to 6500 kHz using a variable capacitor with a range of 10 to 140 pF. We will pick an initial bandset capacitor value of 100 pF. It was mentioned above that we can also vary the coil inductance and coupling coefficient. However, in a real coil, the coupling coefficient between sections and the inductance will be interrelated. Therefore, it's best that we model a real coil rather than pick arbitrary inductances and coupling coefficients. We will pick an air core coil of 20 turns on a 2.5 cm diameter form, and coil length of 3 cm, which will put us in the ballpark at about 7 µH. We will start with a tap position at 9 turns. We will use a wire diameter of 0.01 cm.
We can have the solver adjust any or all of the following parameters:
However we will start by varying only the bandset capacitance and the coil length. Changing the coil length will affect both the coupling coefficient as well as the inductance. If the solver is unable to find a solution, then we can either manually change the other values, or let the solver have a go at changing them all.
The spreadsheet, as it was saved, has these starting values already entered. Input parameters are shown with a yellow background. The ones we intend to let the solver manipulate are cells C12 and C13. The error function cell E17 has an aqua background. (This is the standard colour coding for all example spreadsheets on this site.) All of the intermediate calculation formulae have already been entered. For a detailed description of these formulae, refer to Bandspreading Section 3.
Finally, we are ready to run the Solver. Start by selecting the range of cells C12:C13, and then click the "Solver" button. (This button should appear both in the top tool bar and on the spreadsheet itself. Click either one.)
A dialog window is displayed.
The first field, "Variable Cell Range:" should display the cell range C12:C13 which you just selected. If not, then type in this range.
The next field, "Target Value Cell:" should be set to the error value cell E17 if it is not already set that way.
The "Starting Increment Size:" is the amount that the solver adjusts the input variables each iteration. Use a value of 0.1. This value is not critical, as the solver will automatically adjust it as necessary, while the calculation progresses.
The next field, "Maximum Iterations:" is the maximum number of times that the parameters will be adjusted before the solver gives up. Set it to 1000.
The last field, "Termination Std. Deviation:" determines when a solution has been found. This will be explained later. For now, simply enter the value 1.0E-8.
Now, click the start button, and the solver will start to do it's work. You can drag the Solver dialog around while it's running so that it doesn't obscure any cells that you want to watch. You will notice that the solver may run a bit slow depending on your computer hardware. Part of speed penalty is due to the chart which updates after each iteration. If speed becomes a problem, it's best to delete the charts.
With the starting parameters as chosen, the solver should finish after about 140 iterations. At this point the error function value will be very small, and cells C43 and D43 which contain the actual calculated lower and upper frequencies will be exactly on target. Hence the solution has been found:
CA = 79.29 pF
Coil Length = 2.82 cm
At this point you can experiment by changing the tap position or other parameters and running the solver again to find the new optimum parameters. You can also select the range C12:C16 and allow the solver to change these five parameters. In any event it should result in a useable solution. Note that it may pick a fractional tap position which is perfectly valid. There is no reason why the tap must be exactly at an whole number of turns. However, you may want to pick the closest integer to this value and then rerun the solver with the tap position fixed at this value.
You will note that the variables which the solver manipulates must be together in one contiguous range, either a row or column. You can rearrange these as necessary if you need to manipulate different combinations of variables.
This example has been a bit of a digression from the discussion of inductance formulae, but it's a good entry level example for the solver, and it does provide some closure on the Tapped Coil Bandspread discussion. The next sections will show how the solver can be used to develop and optimize empirical inductance formulae.
Continue to:Part 3b – Empirical Methods