Calculate standard resistor values in Excel

-January 02, 2013

Using standard resistor values can greatly impact the accuracy and predictability of a circuit by eliminating rounding errors incurred at the end of the design. Selecting resistor values traditionally has been a manual process where the calculated value is matched to a real value in a printed lookup table. The Microsoft Excel add-in presented here automates the process by implementing a single user-defined function with an integrated lookup table.

Electronic Industries Association (EIA) values for 20 percent (E6) through 0.1 percent (E192) are stored in an excel spreadsheet (Figure 1). An included user-defined function (Figure 2) stored within the spreadsheet references these values when looking up the required resistance value. The final spreadsheet is saved as an Excel add-In (.xla). Installing the spreadsheet as an Excel add-in allows the function to be used seamlessly with any other spreadsheet.

Figure 1. Excel stores a lookup table containing standard EIA decade resistor values for all desired tolerances.

 ```Function STDEIA(Value, Tolerance, Optional RatioNumerical) 'Function returns closest EIA standard value 'By Donald Schelle, July 2011 'Dimension Values Dim TempIndex As Integer Dim TempMultiplier As Integer Dim ValueMultiplier As Integer Dim Smaller As Integer Dim Larger As Integer Dim RangeString As String Dim RatioNum As Boolean 'Format Value for Processing and determine Valuemultiplier ValueMultiplier = 0 Do If Value < 100 Then Value = Value * 10 'Value is at least a decade too low ValueMultiplier = ValueMultiplier - 1 ElseIf Value >= 1000 Then Value = Value / 10 'Value is at least a decade too high ValueMultiplier = ValueMultiplier + 1 Else Exit Do 'Value is in line End If Loop Until (ValueMultiplier > 13) Or (ValueMultiplier < -13) 'Determine which lookup table to use based on tolerance Select Case Tolerance Case 0.2, "E6" 'E6 Tolerance RangeString = "A5:A197": TempMultiplier = 5 Case 0.1, "E12" 'E12 Tolerance RangeString = "B5:B197": TempMultiplier = 4 Case 0.05, "E24" 'E24 Tolerance RangeString = "C5:C197": TempMultiplier = 3 Case 0.02, "E48" 'E48 Tolerance RangeString = "D5:D197": TempMultiplier = 2 Case 0.01, "E92" 'E96 Tolerance RangeString = "E5:E197": TempMultiplier = 1 Case 0.005, 0.0025, 0.001, "E192" 'E192 Tolerance RangeString = "F5:F197": TempMultiplier = 0 Case Else 'Invalid Tolerance Err.Raise (1) End Select 'Calculate closest smallest number TempIndex = WorksheetFunction.Match(Value, ThisWorkbook.Worksheets("EIA Tables").Range(RangeString), 1) Smaller = WorksheetFunction.Index(ThisWorkbook.Worksheets("EIA Tables").Range(RangeString), TempIndex, 1) 'Calculate closest largest number TempIndex = TempIndex + 2 ^ TempMultiplier Larger = WorksheetFunction.Index(ThisWorkbook.Worksheets("EIA Tables").Range(RangeString), TempIndex, 1) 'Error checking for missing optional argument If IsMissing(RatioNumerical) = True Then RatioNum = False If IsMissing(RatioNumerical) = False Then RatioNum = RatioNumerical 'Calculate closest number based on numerical or ratiometric selection Select Case RatioNum Case False 'Closest Numerical Value Select Case (Larger - Value <= Value - Smaller) Case True: STDEIA = Larger 'Use larger number Case False: STDEIA = Smaller 'Use smaller number End Select Case True 'Closest Ratiometric Value Select Case (Value >= WorksheetFunction.GeoMean(Smaller, Larger)) Case True: STDEIA = Larger 'Use larger number Case False: STDEIA = Smaller 'Use smaller number End Select End Select 'Rescale number with previously calculated multiplier STDEIA = STDEIA * 10 ^ ValueMultiplier End Function ```

Figure 2. VBA code identifies the closest numerical or ratiometric EIA resistance value given a desired tolerance.

1. Click the Microsoft Office Button and select Excel Options.
2. Click the Add-Ins tab. Next to the Manage label select Excel Add-ins. Click the Go button.
3. Click the Browse button. Point to the location of the saved EIA_Standard_Values.xla file and select OK.
4. The add-in (Figure 3) should now be enabled for all spreadsheets.
Figure 3: Installing the code as an Excel add-in ensures that the custom function will be available from any open spreadsheet.

Once installed, using the add-in is as easy as any standard Excel function. The function (STDEIA) has three arguments and returns a single value. The arguments are: 1) an arbitrary value in Ohms; 2) a desired tolerance value (1%, 2%, "E12," "E96," etc.); and 3) an optional value that selects the closest numerical value (FALSE or blank), or the closest ratiometric value (TRUE). Examples of the function are presented in Figure 4.

Figure 4: When installed, apply the code like a standard Excel function making it easy to integrate into existing spreadsheet calculations. The first example (top) looks for the closest numerical 1% (E96) resistance value for 35,534Ω. The second example (bottom) returns the closest 10% (E12) ratiometric value for 34Ω.

An Internet search yields many similar solutions. However, none of them are as complete or as flexible as the one presented here. Edit the code for your own purposes by downloading the .xls spreadsheet at: goo.gl/3C3vS (or from EDN).

References
Standard EIA Decade Resistor Values Table:
www.logwell.com/tech/components/resistor_values.html