How to Use the New Analog Devices Excel Add-In Function for Standard Component Values

Abstract

This Visual Basic add-in file for Microsoft Excel provides a custom spreadsheet function that converts ideal component values into the nearest standard value.

Introduction

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 associated with this application note provides a custom function that converts arbitrary component values into the nearest standard E series value.

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:

  1. Place the .xlam file in the C:\Users\first.last\AppData\Roaming\Microsoft\AddIns\ folder.
  2. Open Excel.
  3. Click the File tab, then click Options. The Excel Options dialog box will appear.
  4. Click the Add-ins category. In the Manage drop-down list, click Excel Add-ins, and then click Go (Figure 1). The Add-Ins dialog box appears.
  5. In the Add-ins available box, select the check box next to the add-in that you want to activate, and then click OK.
  6. To ensure optimum operational compatibility, make any necessary adjustments in the Microsoft Excel Trust Center.
  7. If the add-in function does not always appear in spreadsheets, place a copy of the .xlam add-in file in the C:\Users\[usernam]\AppData\Roaming\Microsoft\Excel\XLSTART folder.

Figure 1. Block diagram of crystal oscillator with internal load capacitors

Figure 1. Block diagram of crystal oscillator with internal load capacitors.

Using the Add-In Custom Function

This add-in has only one new custom function:

Converting from Decimal Values to Standard E series Values


= 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 International Electrotechnical Commission (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.

References

International Electrotechnical Commission standard IEC 60063:2015
https://webstore.iec.ch/publication/22011