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.