How to find resistors from E series for a certain ratio?

Question

Given is a ratio for resistors, e.g. two resistors \$\frac{R_1}{R_2}\$ = "some odd value", like 257:42.

How do I find matching resistor pairs (triples, ...) from E series, without having to try them out one by one?

When I search for the title, I merely find tools, which calculate R₂ if the ratio as well as R₁ are given.

However, I want a way which gives me matching resistor values given only the ratio. I did not find any list/table with all ratios or a calculator tool online.

Finding the magnitude is answered here. Another answer talks about "Sallen Key", which seems to be more complicated than my task.

What am I trying to achieve?
I need a certain gain in my measuring chain. When designing the op-amp circuit, I calculated a certain ratio which is needed for the resistors. Only some standard E series ones are available at the moment.

Answer 1

You can create such a table yourself using Excel and a macro function.

I've created one for the E24 series.

Formulas:

• F3 cell: =E3*POWER(10;$C$2)
• G3 cell: =F3/$C$3
• H3 cell: =GetNearestE24Resistor(G3)
• I3 cell: =100*(H3/G3-1)
• J3 cell: =IF(ABS(I3)<=$C$4;"TRUE";"")

Drag these formulas to populate all rows.

Macro function:

Function GetNearestE24Resistor(Rnom As Double)
    Dim Reff As Double
    Dim Re24(25) As Double
    
    Re24(0) = 1
    Re24(1) = 1.1
    Re24(2) = 1.2
    Re24(3) = 1.3
    Re24(4) = 1.5
    Re24(5) = 1.6
    Re24(6) = 1.8
    Re24(7) = 2
    Re24(8) = 2.2
    Re24(9) = 2.4
    Re24(10) = 2.7
    Re24(11) = 3
    Re24(12) = 3.3
    Re24(13) = 3.6
    Re24(14) = 3.9
    Re24(15) = 4.3
    Re24(16) = 4.7
    Re24(17) = 5.1
    Re24(18) = 5.6
    Re24(19) = 6.2
    Re24(20) = 6.8
    Re24(21) = 7.5
    Re24(22) = 8.2
    Re24(23) = 9.1
    Re24(24) = 10
    
    Dim F As Double  ' Factor
    
    Dim Rl As Double ' Lower Limit
    Dim Ru As Double ' Upper Limit
    
    F = 1
    
    ' Find range of the resistor Rnom within the E24 series
    Do
        If Rnom < Rl Then
            F = F / 10
        ElseIf Rnom > Ru Then
            F = F * 10
        End If
        
        Rl = Re24(0) * F
        Ru = Re24(24) * F
    Loop Until Rnom >= Rl And Rnom <= Ru
    
    ' Find the two nearest E24 resistors
    Dim i
    For i = 0 To 23
        Rl = Re24(i) * F
        Ru = Re24(i + 1) * F
        
        If Rnom >= Rl And Rnom <= Ru Then
            Exit For
        End If
    Next i
    
    ' Find the nearest E24 resisitor
    Dim Dl As Double
    Dim Du As Double
    
    Dl = Rnom - Rl
    Du = Ru - Rnom
    
    If Dl < Du Then
        GetNearestE24Resistor = Rl
    Else
        GetNearestE24Resistor = Ru
    End If
    
    Exit Function
End Function

Answer 2

No Visual Basic, just equations. Here's my solution (scroll right for example):

TABLE 1 (CALCULATOR, horizontal)

Named cell	Equation in code style, other in italics	Value (Example)
Input	Data Input	332.5
Multiplier	=10^INT(LOG10(Input))	100
Scaled Input	=Input/Multiplier	3.325
Target	Dropdown cell with values: 24, 48, 96, and 192	24
Index Exact	=Target*LOG10(Scaled_Input)	12.52
Index Rounded	=ROUND(Index_Exact;0)	13
Output	=ROUND(POWER(10^Index_Rounded;1/Target);IF(Target=24;1;2))	3.5
Output Corrected	=IF(Target<>24;Output;XLOOKUP(0;ABS(E24_Series#-Scaled_Input);E24_Series#;;1))	3.30
Output Scaled	=Multiplier*Output_Corrected	330

TABLE 2 (E24 series, vertical)

Named cell	Equation in code style
E24_Series	={1;1.1;1.2;1.3;1.5;1.6;1.8;2;2.2;2.4;2.7;3;3.3;3.6;3.9;4.3;4.7;5.1;5.6;6.2;6.8;7.5;8.2;9.1}

This is a spilled equation that will expand downwards from a single cell. We use the notation E24_Series# to reference to this dynamic array.

If you prefer not to use this 2nd table, replace E24_Series# with {1;1.1;1.2;1.3;1.5;1.6;1.8;2;2.2;2.4;2.7;3;3.3;3.3;3.6;3.9;4.3;4.7;5.1;5.6;6.2;6.8;7.5;8.2;9.1} in the equations above.

Formatting Note

1. I have used ; (semicolon) for the argument separator, not , (commas).

2. I have also used named cells for this make-shift markup Table so it's easier to understand. In Excel, you can name a cell by clicking on it and changing its name to the left of the equation. They can also be checked in the Name Manager under Formulas.

Step-by-step explanation:

1. We know that the resistor standard series is in a logarithmic scale given the equation \$V_n=\text{round}(\sqrt[m]{10^n})\$, where \$V_n\$ is the rounded value, \$m\$ is an integer of the E series group size, and \$n\$ is an integer of \$\{0,1,2,...,m-1\}\$. Wikipedia - E_series of prefered standard numbers. Therefore, the resulting scaling steps will be between the values of 1 and 10 for this equation in equally-spaced logarithmic steps.

2. Before doing any calculations, we will need to normalize our input to values between 1 and 10. This is done by finding the logarithm base-10 of the input and taking its minimum (floor, or int, doesn't really matter in this case) so we can get the power of 10 that our input needs to be divided by. This is almost similar to finding the minimum number of bits to encode a number: \$bits=\text{ceil}(\text{log2}(n))\$.

3. We will now solve the equation of the standard series. This cell is the Index Exact.

4. Next, the rounding of the index, or finding the closest value to the E-series. Observe that the index is in a linear scale from \$0\$ to \$m-1\$ thus, to find the closest value we just need to round to decimals. This is the Index Rounded.

5. We found the index \$m\$ of our first equation. We can now solve it, but how many decimals does the rounding require? From experience, I can tell you that for E24 we round to \$1\$ decimal place, and for E48 and above we round to \$2\$ decimal places. This is easier to do with our normalized values.

6. The Wikipedia article also tells us that for historical reasons, some of the values of E24 are tweaked. This can be corrected in our output equation by using the XLOOKUP with the match mode 1 to find the next closest value (like rounding). I have used a reference to a spilled array for the E24 series, although it can be replaced with a constant array.Observe how the \$332.5\$ value in the example is detected as index \$13\$ which is the multiplier \$3.6\$, but afterwards it is corrected by finding the best suitable resistor, which is in fact \$3.3\times100\$.

7. Finally, our corrected output needs to be scaled back. We just multiply by the previously computed multiplier.

Images

E24 from 100 to 1000 in steps of 5

E48 from 100 to 1000 in steps of 5

E96 from 100 to 1000 in steps of 5

E192 from 100 to 1000 in steps of 5

Alternative with single equation

Named cell	Equation in code style, other in italics	Value (Example)
Input	Data Input	332.5
Target	Dropdown cell with values: 24, 48, 96, and 192	24
Output	=10^INT(LOG10(Input))*IF(Target<>24; ROUND(POWER(10^ROUND(Target*LOG10(Input/10^INT(LOG10(Input))); 0); 1/Target); IF(Target=24; 1; 2)); XLOOKUP(0; ABS({1;1.1;1.2;1.3;1.5;1.6;1.8;2;2.2;2.4;2.7;3;3.3;3.6;3.9;4.3;4.7;5.1;5.6;6.2;6.8;7.5;8.2;9.1} - Input/10^INT(LOG10(Input))); {1;1.1;1.2;1.3;1.5;1.6;1.8;2;2.2;2.4;2.7;3;3.3;3.6;3.9;4.3;4.7;5.1;5.6;6.2;6.8;7.5;8.2;9.1};; 1))	330

Answer 3

I have faced this tricky little problem many times in my career, and eventually ended up with the solution of putting the E24 series (and 48, 96 and 192) into a spreadsheet. When I want to find a ratio, I fill in the 'ratio' cell, and the 'series' x 'ratio' column updates. Then I scan my eye down that column to look for integer or near integer results. The biggest drawback with that method is remembering where I archived it from last use.

You're unlikely to find E24 components in better than 1% tolerance. For many purposes anyway, that's good enough. Don't let the 'perfect' be the enemy of 'meets specification'.

Resistors are dirt cheap. If you aim to use three from the start, choose a 'shim' resistor nominally 10x the value of one of the others, and when your main resistor ratio is close, adjust the parallel shim to bring it closer. Or you could choose a /10 resistor in series, whichever is more convenient.

Answer 4

Here's my go-to Mathematica tabulator for this:

ratio = 257/42;
maxError = 0.01;

e24ratios = {1.0, 1.1, 1.2, 1.3, 1.5, 1.6, 1.8, 2.0, 2.2, 
   2.4, 2.7, 3.0, 3.3, 3.6, 3.9, 4.3, 4.7, 5.1, 5.6, 6.2, 6.8, 7.5, 
   8.2, 9.1};

e24values = Map[# {1*^3, 10*^3, 100*^3} &, e24ratios] // Flatten;

ratios = Map[{#[[1]]/#[[2]], #} &, Permutations[e24values, {2}]];

errorF[r_] := (r[[1]] - ratio)/ratio
values = Select[ratios, Abs[errorF[#]] <= maxError &];

Block[{$NumberMarks = False},
    StringForm["|`1`|`2`|`3`|\n", 
     NumberForm[#[[1]], 2], #[[2, 1]], #[[2, 2]]]] & /@
  ({errorF[#]*100, #[[2]]} & /@ values // SortBy[First]);
ToString /@ % // StringJoin // "|Error|R1|R2|\n|-|-|-|\n" <> # &

Error	R1	R2
-0.96	20000.	3300.
-0.96	200000.	33000.
-0.86	9100.	1500.
-0.86	910000.	150000.
-0.86	91000.	15000.
-0.13	11000.	1800.
-0.13	22000.	3600.
-0.13	110000.	18000.
-0.13	220000.	36000.
0.57	240000.	39000.
0.57	560000.	91000.
0.57	24000.	3900.
0.57	56000.	9100.