You're probably all too familiar with this drill: you're working on your design, planning out the components that you'll want to place on your board. You'll need resistors, capacitors, inductors. Which means that you'll need to embark on that tedious, yet manual, task of calculating standard component values—better known as the E series of preferred numbers. Not the most appealing part of the project!
Well, I say it's time to say "no more" to these manual calculations. To help us all save some time, I've created an E series Visual Basic add-in file for Microsoft Excel that provides a custom spreadsheet function that converts ideal component values into the nearest standard value. No more manual calculations, and no need to create your own look-up function or to program Excel. Just download the file, and it'll install. In this blog post, I'll provide some more details on how to use it.
Why is the E Series So Important?
Have you ever thought about why we use the E series? Thought a little background might be of interest. The E series is a system of preferred values for electronic components. It consists of the E3, E6, E12, E24, E48, E96 and E192 series, where the number after the E designates the number of values per decade in each series. Although it is possible to produce components of arbitrary value, the E series simplifies vendor inventory and the process of circuit design by limiting the number of available components to a manageable quantity, scaled in a way that is commensurate with the precision of the components.
For example, the E96 table has 96 values per decade, and is used for components with ±1% or better tolerance. For any arbitrary value, there is an E96 value that is within ±1.2%. This provides a reasonably good match while also avoiding redundancy and tolerance overlap between adjacent values.
Components with more relaxed tolerances are usually specified according to coarser tables. Resistors with 5% tolerance are typically provided in values that follow the E24 table, with 24 steps per decade.
The Excel add-In I've created provides a custom function that converts arbitrary component values into the nearest standard E series value.
Turning arbitrary component values into the nearest standard E series value doesn't have to involve a manual process, thanks to an Excel add-in that provides a custom function for this task.
Installing the Excel PMBus Add-In
The E Series Custom Function Add-in provides a custom function to Microsoft Excel (Figure 1). To add it permanently to an Excel installation, follow this procedure:
- Place the .xlam file in the C:\Users\first.last\AppData\Roaming\Microsoft\AddIns\ folder.
- Open Excel.
- Click the File tab, then click Options. The Excel Options dialog box will appear.
- Click the Add-ins category. In the Manage drop-down list, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears. (Figure 1).
- In the Add-ins available box, select the check box next to the add-in that you want to activate, and then click OK.
- To ensure optimum operational compatibility, make any necessary adjustments in the Microsoft Excel Trust Center.
Figure 1. E Series function Excel options dialog box. - If the Add-in function does not always appear in spreadsheets, place a copy of the .xlam Add-in file in the C:\Users\ \AppData\Roaming\Microsoft\Excel\XLSTART folder.
Using the Add-In Custom Function
This Add-in has only one new custom function. It converts from decimal values to standard E series values based on this formula:
=ESERIES(value,[series],[rounding])
- Converts decimal values into the closest standard E series value.
- Arguments:
- Value: An arbitrary decimal value you wish to convert to a standard component value.
- Series (optional argument): An integer that specifies which E series, or more specifically, the number of values per decade. Any integer number of steps per decade can be used, but the standard IEC series are E3, E6, E12, E24, E48, E96, and E192. To select a series, enter just the integer number of values per decade for that series. For example, to convert to E24 value, enter 24 for the "series" argument. If no series number is specified, the E96 table is used.
- Rounding (optional argument): Boolean value, 0 = numeric rounding, 1 = logarithmic rounding. If no rounding is specified, the default is simple numeric rounding to the closest standard value. For example, the non-standard value 3.43Ω is converted to 2.2Ω in the E3 table with numeric rounding, but is converted to 4.7Ω in the E3 table if "rounding" is set to 1 or TRUE for logarithmic rounding.
For More Details
To learn more about preferred number series for resistors and capacitors, you can purchase from the International Electrotechnical Commission (IEC), IEC 60063:2015.
Trademarks
Excel is a registered trademark of Microsoft Corporation.
