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.

Download a copy of the add-in from goo.gl/FxNHX (or from EDN). Follow these steps (Excel 2007) to install the add-in:

  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

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 ti_donschelle@list.ti.com.

Related links:
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

Loading comments...

Write a Comment

To comment please Log In

FEATURED RESOURCES