BITX 2040 Build Update #2: Of Spreadsheets and VFOs

I continue to tweak the capacitors in my new BITX 20/40 VFO (scroll down to see it).  On Sunday I added a little relay that will switch in some additional capacitance to move the VFO about 500 kHz.  Without the additional cap, the VFO will be on frequencies suitable for 40 meters -- with the additional cap it will be on frequencies needed for tuning 20 meters. 

I calculated that I'd need an additional 220 pf to make this shift ( I later went with 100 pf).  In went the needed cap and relay.  It worked.  But there was a problem:  On 40 I had the desired 175 kHz frequency range.   But I found that with this arrangement on 20 I could only cover 95 kHz.   Obviously my variable air cap with 44 pf in series was being "diluted" by the additional 220 pf being switched in by the relay -- with the additional capacitance, the change in total capacitance produced by the variable cap (with series cap) was smaller in the 20 meter range than it was in the 40 meter range. 

Clearly, one solution was to play with the value of the cap that I had in series with the variable cap -- increasing it would increase the freq spread (both on 40 and 20).  But how much should I increase it?

By this point I was getting tired of all the manual calculations.  Time for a spreadsheet! I created one, and threw in the values of all the caps and of the coil,  and the resonant frequency formula.  I set it up to display and the resulting freq coverage. 

The freq ranges in the actual ham bands is a bit off -- there may be some stray capacitance and inductance in the circuit that is not being captured in my numbers.   But the important thing was the spread.  The spreadsheet allowed me to see that with a 100 pf cap switched in by the relay and a 74 pf cap in series with the variable, I could cover 180 kHz on 40 and 123 kHz on 20.  That's OK.  This little exercise shows the usefulness of spreadsheets in dealing with this kind of calculation.

You can see my spreadsheet here:
https://docs.google.com/spreadsheet/ccc?key=0Ak4BJL1-oWiJdElieDY5Y2xVWUtSNDl0anRYLVBBeGc&usp=drive_web#gid=0

Our book: "SolderSmoke -- Global Adventures in Wireless Electronics" http://soldersmoke.com/book.htm Our coffee mugs, T-Shirts, bumper stickers: http://www.cafepress.com/SolderSmoke Our Book Store: http://astore.amazon.com/contracross-20