THE MAGAZINE FOR COMPUTER APPLICATIONS
Circuit Cellar Online offers articles illustrating creative solutions
and unique applications through complete projects, practical
tutorials, and useful design techniques.

DO YOU EXCEL IN ELECTRONICS?

Part 2: The Benefits of Using Excel
by Aubrey Kagan

Start ý Standard Values ý Saving Macros ý Simple Amplifier ý LM317 Calculation ý Goal Seek ý Use of Functions ý Additional Options ý Source and PDF

The calculation of resistor values for an op-amp gain stage or an LM317 voltage regulator is easy enough to do on a napkin with a calculator, so why use Excel? Aside from the obvious economy of not wasting a napkin, there are other advantages to be gained.

Excel allows you to expand the calculation to include worst-case scenarios. It also automates the entry process by creating a model (with macros if necessary) so that you no longer have to open the data book or spin the CD-ROM every time you want to check the formula or get a complete analysis of the circuit. After you have mastered the simple models, the techniques can be applied to more complex ones. Additional benefits may be obtained by using incidental macros, like the Standard Resistor Value macro that I start off with this month.

At one time, macros were just a method of repeating a certain sequence of keystrokes (long before the use of a mouse). They can be just as useful today. A common action in a spreadsheet, such as creating a heavy border around a table, can take at least five mouse clicks after the block is highlighted to be achieved. Because this is a common action, you can train Excel to mimic your actions. These actions can be condensed to a name or even a button on the button bar, letting you repeat the macro until the cows come home. This is covered in almost any book about Excel.

Over the years, applications such as with clerical data entry have needed additional functionality. Different macro languages evolved until finally Microsoft integrated Visual Basic as its standard macro language for all of its applications. This made the macro powerful indeed. Although Excel comes with copious functions built in (including engineering-targeted applications like Fourier analysis), thereýs always room for more specific types of applications. Use of these macros is treated exactly like calling a subroutine in software, and it is possible to pass and return parameters to and from them.

A calculated resistor value almost never works out to a standard resistor value. In times past, after a calculation, I would look up a standard resistor value and then use the standard value to recalculate, but today this can be included as a custom function. I have included with this article three macros that will allow you to look up the nearest standard value for the A series resistors (5%), the B series resistors (1%), and potentiometers. Even if you choose not to follow how these are created, you may still find it a desirable addition to your spreadsheet toolbox. Later examples I discuss use the macro as an additional function without considering the internal operation.

NEXT