How to use it:
Enter the frequency, load and source resistances in the marked cells.
Note: Data you enter is RED and cells calculated by the spreadsheet
are BLUE (and should be locked).
Now, starting from the load end of your network, enter one component on
each row with the two letters that describe it. The Element Value cell
holds the component's value in farads, henries, or ohms. So if you are
entering nn picofarads, you should enter nnE-12. Enter nn microhenries
as nnE-6, and so on. [pico = E-12, micro = E-6, nano = E-9, milli = E-
3]
Under component C, L, R, Xn enter the appropriate capital letter. If
you have crystals enter them as X1 through X10. Enter 1 for the value
of crystals. (Press the SHOW button to bring the crystal data area
closer.)
Under (S)eries or P(shunt, or parallel) enter a capital S for a series
element and a capital P for a shunt element.
After you have entered all the element data, enter the source
resistance as if it were a separate series resistor. For a 50 ohm
source, enter 50, R, S.
Enter the source and load resistances and the frequency in the labeled
cells. All results will be displayed, except if your network uses
crystals. The routines for crystals are executed by Visual Basic when
the plot button is depressed.
To plot a frequency response, enter the desired minimum and maximum
frequency and press the PLOT button. Then click the "Response Plot"
tab. If the plot doesn't look right, you may have a component entered
incorrectly or you may have manually set some plot parameters for the
graph that are no longer valid. There's a further discussion of
graphing below.
Caution: Experienced Excel users like to clean up their spreadsheets
by deleting entire rows and columns. Don't do that with this sheet.
There are absolute cell references in the cell formulas and in the VBA
code that would be invalid if you deleted any rows or columns. You can
delte data by highlighting the range and pressing DELETE, but don't
delete the entire row/column.
What it does and how:
This spreadsheet calculates the equivalent impedance looking into a
ladder network using the method described by Wes Hayward in
Introduction to Radio Frequency Design (IRFD). It also calculates the
transfer function at a single frequency, and plots the gain (frequency
response) over a range of frequencies.
The user specifies a frequency, and starting from the load, enters
component values and types (C, L, R, Xn) and configurations (Series or
Parallel). The spreadsheet "program" starts from the load and moves
toward the source, calculating the impedance at each point as each
component is added. At the end, the Z looking into the network is
seen. There is no practical limit to the number of components in the
network, but I've allocated 58 rows. Also, there is no requirement for
components to alternate series, parallel, series and so on.
Excel's ability to simulate the decision branching of a programming
language with the IF statement is utilized. Flags indicate whether the
entered value is a resistor, capacitor, or inductor and the program
calculates the impedance accordingly. A second flag indicates whether
the component is connected in series (S) or shunt (P) so that the
appropriate formula can be used to combine the new component with the
"running total".
Excel's ability to perform complex number operations is also used.
(Insure Analysis ToolPak is installed under Tools, Add-Ins.) One
limitation is the fact that Excel displays complex numbers as text,
with all significant digits shown. This makes results difficult to
read, so the spreadsheet converts them back to real numbers and
displays them in a more readable format in some cells. I also provide
a button to allow hiding all those appalling intermediate calculations
from view.
The usefulness of a ladder analysis program comes from the fact that
the configuration appears so often in RF (and sometimes audio) designs
of impedance matching networks, filters, and networks that perform both
of these functions. Some common circuits that can fit the description
of a ladder network include PI networks, many bandpass filters, L
networks, top coupled parallel resonators. Even a single series or
parallel component is a primitive version of this network type. Ladder
type crystal filters are a special case that can be accommoded directly
if the parallel C is ignored, or modeled with complete accuracy if the
spreadsheet is altered to allow a new component type for the crystal
(Coming soon) (DONE!).
Calculating frequency response:
In addition to calculating impedances, the sheet also calculates a
transfer function. (Actully a transfer constant rather than a
function, since it is evaluated for a single frequency.) The transfer
'function' multiplied by the source voltage gives the output (load)
voltage. This can then be converted into gain. A Visual Basic routine
is provided to input 200 frequency values (range by user) into the
sheet, save the resulting gain and phase numbers to an array, and plot
the resulting frequency response in a chart in the "Response Plot" tab
of the workbook.
I'll attempt to explain the method of calculating the transfer function
as described in IRFD. The transfer function is the product of a series
of impedances and admittances. These Zs and Ys are the values looking
into the network toward the load when it is broken at each series to
parallel transition. When looking into a series element, an admittance
is used. When looking toward a parallel element, an impedance. The
first (parallel, impedance) element is that of the load and any network
elements in parallel with it. The final (series, admittance) element
is the value looking through the source resistance toward the load,
from the source itself. (The source resistance is considered as an
element of the network, and is entered on the final line as a series
resistor. The load is also a part of the network, but its value is
only entered once in the separate data area.)
Rather than calculate admittances, I just accumulate "series" and
"parallel" impedances separately and finally divide the product of the
"parallel" impedances by the product of the "series" impedances. That
last step being equivalent to multiplying by the "series" admittances.
So the columns headed "TF partial, parallel" and "TF partial, series"
are actually just intermediate values. But they do represent the
actual Z of the network, broken at that point and looking toward the
load, and so are interesting in a way.
I had to deal with the fact that there may be two or more elements in
series (or parallel) in successive spreadsheet rows. Such rows don't
represent a transition from series to parallel (or vice-versa) in the
network and therefore don't get included as an element in the
calculation. So the sheet is set up to put 1s into the cells for such
instances, so as multipliers in the final calculation, they have no
effect. You'll only see calculated entries where there is a transition
from P to S or S to P when going to the next row. (Also the final row
[source resistance] produces a calculated value.)
Needless to say and fortunately, you don't need all that tedious
explanation to use this spreadsheet.
Concerning the gain values produced. You'll note that a lossless
filter in its passband shows a voltage gain of 0.5 and a gain (actually
loss) of 0 dB. That's because the gain is defined as the transducer
gain (Gt). This is the ratio of the power delivered to the load
divided the maximum available power. Recall that the maximum power
transfer from a source having real source resistance occurs when the
load R equals the source R. Under this condition, the voltage at the
load is 0.5 times the source voltage. That's why voltage gain is 0.5
at this point and also why it is defined as the 0 dB gain (attenuation)
point. The 0 dB point is where maximum power is delivered to the load.
Problems?
Cell is locked--I can't change it!
I locked cells containing formulas to prevent accidental changing or
deleting of necessary information. However, if you want to correct
errors in my work or make improvements, you can unprotect the
worksheet--it is not password protected. Just go to the Tools menu,
choose Protection, and Click Unprotect Workheet. [Forget all that for
now--locking is causing my Visual Basic routines problems. Nothing is
locked.]
The plot doesn't look right; the plot is blank.
I haven't attempted to program functions to automate the look of the
graph because Excel does a pretty good job of that by itself. But it's
tempting to tweak the graph to make it more easy to read. Excel makes
the X and Y axes ranges wider than needed. You can right-click in the
vicinity of the numbers of either axis and choose "Format Axis" from
the mini-menu. Then click the Scale tab and un-check Auto for the
Minimum and Maximum values and type in your own scale limits. This
allows you to focus in on the range of interest. If the grid markers
aren't spaced close enough, you can also un-check Auto for the Major
Unit box and type in a smaller one.
There's a lot more about making Excel graphs look right than I can try
to cover here. For example, I don't like having the axis markings
inside the graph area. When you choose "Format Axis", you need to set
the "intersects X [Y] axis" parameter to prevent this. Play with the
graph by right-clicking the X or (and) Y data and choosing "Format
Axis" and also by right-clicking inside the graph and choosing "Format
Plot Area" or "Chart Options". Experiment.
Here's where a problem can arise--if you manually set graph parameters
for one circuit, then attempt to plot another one without setting the
values back to Auto, your new data may not lie on the graph. Best to
set those parameters back to Auto to begin with for each new circuit.
What if that's not the problem and there's still nothing on the graph?
Double check all of your component values, types, Series or Shunt
designations, frequency range and Source and Load resistance entries.
Note that the Plot function over-writes impedance formulas for cells
with crystals. If you enter data for a new circuit after plotting one
with crystals, you need those formulas restored. The Plot button
restores them, and so does the Reset button.
Look at the Minimum Attn field. If it's showing -100 dB and you
expected a passband with minimal attenuation, something is definitely
wrong. Try looking at the calculated component Z for each component
and see if anything jumps out as wrong.
Saving circuits:
I haven't automated the ability to save circuits that have been
entered, but I will describe the "manual" way for those not that
familiar with Excel. I've reserved an area in the "Ladder" sheet
starting around X400 for saving the information. I've also provided a
link at the top of the sheet to take you there in a hurry, and another
one to bring you home.
Click in cell D22, hold the mouse button and move over to F22, then
while still holding, move down to the last row of data (or beyond is
OK). Release the button. All the data should be highlited at this
point. Press Control-C to copy the data to the clipboard. Now go down
below X400 and find the next availabe clear space below the filters
already saved. Leave a few extra rows above for a title and comments.
Click in Column X, at the upper left hand corner of where you intend to
put the data table. Now press Control-V. Be sure to type in a good
descriptive title in the cells above the data. Include the source and
load resistances and perhaps the recommended frequency range for
plotting.
Now, sorry I didn't put the crystal data area in adjacent spaces. You
have to move that data separately if you have crystals. I put it
immediately to the right of my main data table. I generally copy the
headings and labels too, but that's not necessary.
To restore a filter, do the reverse--copy the table, and paste it back
into the data area of the spreadsheet. Don't forget to manually type
in the Rsource and Rload values--failure to do so has already caused me
a lot of confusion more than once when I was re-examining a saved
filter. Also--push that RESET button before (or after) setting up a
new circuit.
Odds & Ends:
On crystals. Some analysis methods might ignore the parallel
capacitance, Co. This one takes it into account for greater accuracy.
Use 5 pF if you don't know the exact value. It's also common to use
one set of average crystal parameters for a set of closely matched
crystals. That works OK, but this spreadsheet allows you to enter the
specific data for each crystal, if you choose. If you want to use
average data, just enter X1 data only and re-use the X1 symbol for as
many crystals as you have in your filter. By the way, Co is to blame
for the characteristic "lower sideband" shape of ladder crystal
filters, in which the higher frequency side slopes off faster.
(Thanks, K8IQY for that info.) When you focus in closely on the
crystal's response, this assymetry is all you notice. But if you
expand the plot a bit, you see a separate null area caused by the Co.
Notes in cells: I put notes in some cells to explain what they
represent and how to use them. Excel marks them with a little red
flag. When you put your cursor over the cell, you see the note. But
sometimes the note is too long to fit in the little box. In that case,
if you want to read it all, click in the cell, then press SHIFT-F2.
That opens the note. Then you can use your mouse to enlarge the note
box. Hit ESC twice or click in a different cell to make the note go
away.
Crystal filter mesh frequencies: Normally, filters are built using
identical crystals, with their frequencies closely matched. A
requirement for a filter is that the frequencies of the meshes be
identical. A mesh is a series circuit including a crystal and the
shunt capacitors to the left and right of it. The series capacitance
causes the frequency of the mesh to be higher than the crystal
frequency. That might be OK, except that different meshes have
different amounts of shunt capacitance and so end up with different
frequencies. If the difference becomes large enough to adversely
affect performance of the filter, a correction is usually made by
adding series capacitors to the crystal(s) having the lowest mesh
frequency, raising them to equal the highest mesh frequency.
This spreadsheet attempts to address this issue. In the crystal data
area, provision is made for entering the value of the mesh capacitors.
The resulting offsets are calculated and displayed, both from the
crystal's own natural frequency and from the frequency of the lowest
frequency mesh. The mesh showing zero (0) offset is the one that might
need to be raised. The spreadsheet also calculates how much series C
it would take to raise the frequency. Reasonableness checks aren't
made, so meshes that don't need adjusting will show a large C value.
Those with significant mesh errors will show smaller Cs and might need
to be adjusted. Use this information for experimentation. If your
filter's response shape doesn't look right, try inserting the
recommended series C.
A filter with no shunt C on the end will only have one mesh capacitor.
In that case, put in a large C value for the other one--simulating a
short circuit.
Another thing you might be able to do regarding mesh frequencies,
assuming you have data for each crystal, is to arrange your crystals so
that those with the lowest natural frequency are installed in the
meshes with lowest capacitence, and so on. You may be able to exploit
the deviations among your crystals to get the mesh frequencies
equalized without using capacitors.