Excel spreadsheet yields RLC best-fit calculator
Excel lets you automate engineering functions to find best-fit values of resistors, inductors, and capacitors.
Alexander Bell, PhD, Infosoft International Inc, New York, NY; Edited by Martin Rowe and Fran Granville -- EDN, May 28, 2009
Commercial off-the-shelf software such as Microsoft Excel lets you automate engineering functions (references 1 through 3). This Design Idea explains how you can use Excel to calculate the values of two passive components—resistors, inductors, or capacitors—from the standard E-Series, which comprises E6, E12, E24, E48, E96, and E192, that you can use in circuits such as filters. The application’s results depend on whether you select a parallel- or a series-connected topology.
The calculations appear in an Excel spreadsheet. The VBA (Visual Basic for Applications) source code for this project resides in a single code module (Listing 1). It contains three main public functions, FitR(), FitL(), and FitC(), and several private auxiliary functions. The key algorithm loops through the range of values, trying to find the best fit for the target. There is an inner loop for the first value of RLC and an outer loop for the second one.
Figure 1 shows the user interface. You can enter the user-defined functions FitR 1234, P, or E192 into any cell of the Excel worksheet. The cells accept four arguments and return a text string containing the best-fit values, R1 and R2 in this case, and the relative error of approximation. Table 1 shows the functions’ parameter list. For better readability, the spreadsheet returns the values of R1 and R2 in commonly used electrical-engineering format by applying a scientific-to-engineering format-conversion function, E2BOM().
|
The computation engine for electrical resistance and inductance components uses the same formulas: a simple sum of the resistance for the series connection and a sum of conductance for parallel topology, whereas, in the case of the capacitors, the formula is vice versa. You can also fine-tune the functions by changing the constant values corresponding to the upper and lower search limits (Listing 1). Thus, you can extend the search range and increase the accuracy, although this process requires more computation time. If you use Microsoft Office 2007, you must contend with an increased security level and set the proper permission level to run the VBA content of the Excel workbook.
This approach is essentially a desktop application, extending the functions of the popular Excel application. You can install the application on either a computer or a network. To further extend its accessibility and bring it to the global level, you should consider an online Web application. The modern RIA (rich-Internet-application) concept and corresponding development tools, available on the market, let you build Web applications with the level of interactivity and responsiveness close to those of the desktop application. A Web-based application provides for easy implementation and maintenance. The user needs only a Web browser. Web applications are essentially platform-independent and globally accessible. Web-based applications of the RLC calculator don’t require the user’s machine to have MS Office. You can also place the application in password-protected directories from which you can control access to them. A demo version of an online RLC best-fit calculator incorporates the latest set of Microsoft technologies, such as ASP.NET, C#, and Ajax, providing a rich user experience with high interactivity and responsiveness (Reference 4).
| References |
|
-
AS FYI: online calculators are available at: www.webinfocentral.com/MATH/Calculators.aspx
Alex Bell - 2009-25-6 20:05:00 PDT -
Bill,
Thanks for your input. There are couple easy workarounds to the issue you've reported:
1). As an immediate fix, use the online version of the RLC calculator, available at: www.alexanderbell.us/RLC/RLC.aspx
2). Go to the VBA source code and change the line, which sets low limit for L:
Private Const MIN_L_PREF As Integer = -7 'PREF L LOW LIMIT (ORDER OF 10)
Set it either to -12 or -15 up to your preferences.
Hope this will help.
Best Regards,
Alexander Bell
Alexander Bell - 2009-9-6 17:11:00 PDT -
Interesting application of Excel, but note that it won't do inductors below 100 nH - an area where us RF types hang out.
Thanks for sharing the spreadsheet!
Bill
Bill Ress - 2009-1-6 15:06:00 PDT -
Thanks to Kirby for the comments. I just want to highlight one of the core ideas of the article regarding the possible evolution of the Excel-based application (quote) "... To further extend its accessibility and bring it to the global level, you should consider an online Web application...The user needs only a Web browser. Web applications are essentially platform-independent and globally accessible. Web-based applications of the RLC calculator don’t require the user’s machine to have MS Office... ". The article also provides a link to functional web-based demo application at: www.alexanderbell.us/RLC/RLC.aspx
Alexander Bell - 2009-29-5 06:46:00 PDT -
Another open source application similar to this is the rombos project at sourceforge. rombos.sf.net
The executable runs directly on win32 platforms and does not require any other software. Not as refined as this new one, but lists several combinations to allow for BOM reductions.
Kirby Goulet - 2009-28-5 10:18:00 PDT
