Excel spreadsheet yields RLC best-fit calculator

-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().

See all of EDN's
Design Ideas

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).

  1. Bell, Alexander, “Add CAD functions to Microsoft Office,” EDN, March 21, 2002, pg 94.

  2. Bell, Alexander, “Simplify computer-aided engineering with scientific-to-engineering conversion,” EDN, Sept 30, 2004, pg 94.

  3. Bell, Alexander, “Voice feedback enhances engineering calculator,” EDN, July 11, 2002, pg 108.

  4. Bell, Alexander, “Best Fit RLC Calculator.” 

Loading comments...

Write a Comment

To comment please Log In