Accountants and MBAs use spreadsheets to play “what if” scenarios with business and financial data. Can you do the same thing with electronic circuits? The answer–perhaps not surprisingly–is yes.
Consider this simple common emitter amplifier (I modeled it in PartSim, if you’d like to open it):
In this particular case, there are several key design parameters. The beta of the transistor (current gain) is 220. The amplifier has an overall voltage gain of about 3 (30/10). I say about, because unless the transistor is ideal, it won’t be quite that. The supply voltage (Vcc
) is 12 volts and I wanted the collector voltage (VC
) to idle at 6V to allow the maximum possible positive and negative swing. I wanted the collector current (IC
) to be 200mA.
Design by Math
So how do you select the values of the resistors? Start with R3
at the collector. I specified that VC
=6V and Vcc
=12 V so R3
has to drop 6 V. If VC
had been, say, 9 V then the drop would be 12-9 or 3 V. I also said that the collector current, IC
, had to be 0.2 A. Ohms law, then, says R3
= 6/0.2 = 30 ohms.
The gain in this configuration is going to be R3
/R4
. So if the gain is 3 and R3
is 30, it stands to reason that R4
is 10. The current through R4
is almost the same as IC
. It is actually related by the alpha of the transistor which is related to the beta. In a transistor like this, the alpha will be nearly 1.0. That means the emitter current will be equal to slightly more than the collector current. For a quick calculation, let’s assume they are the same so the voltage at the emitter is going to be 0.2 times 110 or 2 V.
The voltage at the base, then, needs to be about 0.7 higher or 2.7 V. Because the transistor model is accurate and it models a real transistor, I actually want to set the base voltage to about 0.8 V higher than the emitter and that’s 2.8 V. The base will look like the emitter resistor times beta in ohms (2220 ohms). So the voltage divider of R1
and R2
really has 2200 ohms in parallel with R2
. To get the 12 V knocked down to 2.8 V needs R1
=202 ohms and R2
to about 64 ohms. That’s it! You now know all the values and PartSim (or another circuit simulation tool) will show that it works. The 1 kHz signal is just for testing and you can see you get roughly a gain of 3 from input to output (along with a 180 degree phase shift):
Spread the Joy
While those steps aren’t very complex, it is tedious. Why not use a spreadsheet (download amp.xlsx from GitHub) to capture the algorithm? Then you can easily make changes and see the results instantly. Here’s an example:
What happens if you change the IC
to 0.1 A? Or the gain to 5? Or the input voltage to 24 V?
There are two cells that you probably won’t change much, so they aren’t under inputs, but they do affect the overall design. The first is the Vbe
drop. For a normal silicon transistor, this should be about 0.6 or 0.7 volts, dependant on temperature. If the real circuit or an accurate simulation shows a different voltage drop between base and emitter (for example, the base is at 2.8 V and the emitter is at 2 V), use that difference (0.8) in the parameter and you’ll get a better result.
The second cell is the current in the voltage divider (R1
and R2
). The total current through R1
is equal to the current through R2
and the base of the transistor. The base looks (more or less) like the emitter resistor multiplied by the beta of the transistor (which isn’t a very stable parameter). In theory, you could use a lot of different values of the voltage divider resistors to get the correct ratio. The spreadsheet assumes that the total current will be a fixed multiple of the expected base current. If you set this multiplier too low, you’ll get a negative resistance, so you’ll have to raise it.
In general, if you set the total current to, say, four times the base current, then R2
will get three times the base current through it. A reasonable value is 10 which ensures that changes in the base current won’t affect the divider output very much.
Of course, the spreadsheet won’t pick standard resistance values. That’s fine for simulation, but if you are really going to build, you might want to get close values. For example, to use a 47 ohm collector resistor, you could adjust the quiescent voltage or the collector current. For example, try the example with a collector current of 0.127. That results in values of 47 ohms and 16 ohms, both standard 5% resistors. For the dividers, you can play with the current multiplier. Continuing on the example, setting it to 11 puts the divider in range of a 1.5K resistor and a 510 ohm resistor, both standard values. Remember, device parameters will vary along with part tolerances, so getting close is good enough (and if it isn’t, then you have a problem anyway because of variations due to the manufacturing of the transistor, temperature, and other effects).
Speaking of tolerances, it is easy to look at the effect of tolerance ranges using the spreadsheet. With a little work you could even repeat the math on a single spreadsheet to catch all the end cases.
Solve Your Problem
Many spreadsheet programs can also solve optimization problems. Next time, I’ll show you how you can use that in conjunction with models of your circuit to easily find component values.
I use a similar method, only I use self-contained HTML pages with javascript which can be embedded into other HTML pages.
Me too, angular makes trivial easy to make js applets. Js is way more portable than excel.
Next up: Excel-lent PCB layout.
Oh dear…
checking the effects of tolerances is a nice reason to slap this into an actual program, using some library to get interval arithmetic support.
moving it into a real program would also let you apply optimization algorithms, and save yourself the hassle of plugging in different values. want to stick near standard component values? penalize solutions the more they differ from those values. or, hell, have it directly compute the cost of the parts and pcb area necessary to hit the specified component values, and optimize for some function of quality / price.
It’s possible to use optimization in vanilla excel too. There are built-in options that will let you (dangerously) step through different values until it’s optimized to your needs. You’ll get circular reference errors, but if you go slow it won’t be anything catastrophic.
For the advanced excel user there’s the prepackaged Solver that ships w/ excel, or custom VB scripts.
For the corporation it makes sense to just buy the right software, but for the hobbyist it may be worthwhile (as a learning tool, or due to money) to use excel.
A natural extension of this method would be to use a program to script runs of a SPICE simulator instead of plugging manually-derived analyses into a spreadsheet. I think LTspice can even be run from the command line – wrap some python around it to set E24-series component values and sweep over component tolerances, and you’ve got a decent virtual cut-and-try design tool. You can do a lot of this with parameter sweeps in LTspice already, but this would open up some nice output options – for example: what is the probability that the gain of this circuit is out-of-spec if I choose 5% tolerance resistors for all parts. Unless you’re a worst-case-design type.
If there is a probability that the circuit is out of spec with the specified component tolerances, you’ve designed it wrong. In a long-enough production run, you will end up with out-of-specs units that will fail anyhow.
Unless you’re an asshole and knowingly push out bad products, knowing that the customers won’t notice or won’t complain, or the problem will surface only after the warranty is over. An example would be laptop manufacturers who allow 25% 3-year failure rates and simply patch it up with on-site warranty sold separately.
That would never happen!
Well, except for all the times it has…
For maximum output swing, you want the resting voltage at something like 5/8ths of the powersupply: the bottom 1/4 is used by the feedback system.
Depends on the gain.
PartSim is a Digi-Key “Cloud App”. So what is this HaD? PAID Placement Post?? If is an Ad, then say so Up-Front, if it isn’t, say so Up-Front!
For the rest of the readers – my recommendation:
Learn SPICE. There are several ways in SPICE to “solve” for unknowns by iteration/analysis. The “easy” entry to SPICE is through the application LT-Spice from Linear Technologies. LT-Spice is not Open Source, but completely free and unencumbered – and yes, it works Fine-in-Wine for those running Linux/xBSD on the desktop (LT-Spice is Windows Native by default, and still works fine with WinXP as of my post date). Plus you can import most common non-proprietary component models in LT-Spice. You’re not “tied” only parts from Silicon Labs. Finally, there’s a very good LT-Spice Yahoo Group with lots of help and a large library of tutorials and models.
PartSim is a free program they mentioned twice. You might as well accuse them of shilling for MS since they linked to an *.xlsx file
You caught me! Have you ever noticed, you never see me and Bill Gates together. Very suspicious!
@Leithoa…
“PartSim is a free program they mentioned twice. You might as well accuse them of shilling for MS since they linked to an *.xlsx file”
Really? I found no place to download “PartSim” for native use. Maybe I missed something, or (likely) the download link (if it in-fact exists) is buried under layers and layers of scripting (I block scripting by default and will only dig through the layers selectively – if ever). And even if PartSim is downloadable, is it really “Free” as you state? Or does downloading depend on the source collecting your personal information first (e.g., Email and/or Social Media nonsense)?
Oddly enough I did a three part series on LTSpice this year: https://hackaday.com/2016/02/26/adding-spice-to-your-workbench/ and https://hackaday.com/2016/02/29/spice-power/ and https://hackaday.com/2016/03/02/transforming-spice/ along with some videos on the Hackaday YouTube channel.
So, yeah, I like LT-Spice, although I might not be the fan boy you appear to be. On the other hand, I don’t think anyone interested in reading about circuit design in Excel is going to go install LT-Spice just to check out a simple amplifier. The nice thing about the Web-based versions is that you can click a link and they run. I’ve also written about Falstad (another 3 parter: https://hackaday.com/2015/07/20/a-breadboard-in-a-browser/), EasyEDA (https://hackaday.com/2015/08/21/a-tale-of-two-browser-pcb-tools/), and a lot of other choices.
We have editorial freedom, but in this case, that tool was a great choice for this task. There are others–many of which aren’t free for long–but this one did the job, works well, and its free. It wasn’t even the highlight of the article.
>You’re not “tied” only parts from Silicon Labs.
It is Linear Tech, not Silicon Labs.
“If is an Ad, then say so Up-Front, if it isn’t, say so Up-Front!”
If it were the former, we would. (But we don’t do paid content ever.)
If it’s the latter, you’re asking us to disclose that there’s nothing to disclose. We just took the short-cut and didn’t disclose the nothing.
In other news, I didn’t shoot any kittens today.
Disclosing that there’s nothing to disclose. Seems reasonable. Is that a Linux thing?
You’re using wrong arguments to deliver a potentially valid message.
Cloud applications are bad not because they come from this company rather than that one, not because someone might be paid to publicize them, but because they’re essentially a faucet you have no control on between you and your data. What happens if the parent company goes belly up, decides to charge money or take ownership of all your work?
Cloud applications are the way some entities can take control of your data, and they’re extremely bad for this only reason. Please don’t encourage people to use them.
I’ve been using spreadsheets to solve circuit problems since VisiCalc. Good article.
How does one make excel to use names (instead of cell reference) to call value. For example value of beta is called in formulas as BETA instead B2. I consider my self quite advanced user of excel, but I never saw something like this, nor I can reproduce it.
You go to the cell B2 and click on the text box on the menu bar. You can change the cell B2 to BETA there. The long way is to use the Insert/Name menu and that’s the place you delete the name. All explained from help.
sims are great for digital and ok for analog, but the real world shows up and gives you all the “other variables” that your sim wont account for and the circuit that’s works great in your sim will probably not work and give puppies cancer to boot.
And using excel for engineering work is the bane of my existence because I am forced to do so quite often… and while I can suffer hours of fussing with excel and get something workable, I’d be much happier with a real, honest to goodness set of engineering software tools.
You simulation is only as good as your physical model and the solver algorithm. If you operate a simplified model outside of its intended range or not have enough details, then you’ll get garbage. Simple as that. Modeling is hard work. There are companies that that physical measurements of parts to make better behavior models.
LTSpice is okay, but the solver isn’t as good as some of the propriety (paid) simulator that have tweaked solvers for convergence. LTSpice sometimes chocks and give up. On the other hand, it is free and closer to engineering tool. I have done power supplies design using it and given good enough models agrees with my lab measurements.
Well, not that they were done in Excel, but there is an awful lot of money spent on simulations. Like any tool, you do have to understand their limitations. In my 30 years as an engineer, I’ve seen simulations go from specialized software running on big computers with a team of people, to having reasonable sim running in a Web browser. Pretty good stuff as long as you keep grounded on how good your sim is and where it breaks down.
So I guess trial and error is the best approach. At least the puppies will be ok.
How does one make reference to the cell in excel using name instead of excel reference (e.g. beta is always called as BETA in formulas instead B2). I use excel quite a lot and know few neat tricks, but I never saw this one.
I’ll just leave this here, again.
ASCO A SPICE Circuit Optimizer
http://asco.sourceforge.net/doc/asco.html
Don’t forget the internal emitter resistance, Re, which is equal to 25mV divided by the emitter current. That is the major reason why gain is a bit less than expected. It becomes especially important in low quiescent current circuits, since Re is inversely proportional to emitter current.
If you use one of the many SPICE variations, this should be included in the models.