Calculate standard resistor values in Excel
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.
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
- Click the Microsoft Office Button and select Excel Options.
- Click the Add-Ins tab. Next to the Manage label select Excel Add-ins. Click the Go button.
- Click the Browse button. Point to the location of the saved EIA_Standard_Values.xla file and select OK.
- The add-in (Figure 3) should now be enabled for all spreadsheets.
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.
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).
Standard EIA Decade Resistor Values Table:
About the Author
Donald Schelle is an Analog Field Applications Engineer for Texas Instruments power group and has more than a decade of engineering experience. He received his Bachelor of Electrical Engineering from Lakehead University, Thunder Bay, Ontario, Canada. Donald can be reached at email@example.com.
Programs calculate 1% and ratio-resistor pairs
Selecting the best resistor technology for the application
Five questions about resistors
Resistor Noise—reviewing basics, plus a Fun Quiz
The hidden variable: circuit stability as a function of resistor stability
Precision resistors play vital role in analog circuits of seismic instruments
Design femtoampere circuits with low leakage - Part 2: Component selection
Resistors in aeronautics applications: Meeting the new performance requirements