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

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 R2 if the ratio as well as R1 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.

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.

• There are online calculators for the very purpose. I used to have a shareware (yes, shareware) desktop application for the very purpose. Very useful. Gladly paid the 5 bucks for it. Sep 5, 2022 at 21:11
• What tolerance resistors are you considering using? Will you be happy with the nominal value ratio being correct (rather than measuring and calibrating the resistors)? Sep 5, 2022 at 21:12
• Resistor Value and Ratio Calculator Sep 5, 2022 at 21:52
• Use 11k and 1.8k, it's accurate to within 0.13% :-) I wrote a small Perl script to do such calculations about 20 years ago; I use it all the time. But there are online calculators, as mentioned above. Sep 5, 2022 at 22:00
• Resistor optimizer Sep 5, 2022 at 22:11

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


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
• Ah yes, I forgot to mention.that my answer is a replacement for the top-voted answer's VBA function for a single input. Of course, this can be extended to a table nontheless. I usually prefer not to use VBA whenever I can. Sep 13 at 14:08
• So this is a pretty neat approach to finding the E series resistor value for a desired resistor. A few comments: When I paste this into Excel, the semicolon/comma swap doesn't work for me - I had to change most of the semicolons to commas for Excel to recognize the equations properly. Second, you should probably be using FLOOR on Index_Rounded, if you're looking for the closest value - putting 332 in Input for an E24 gives me 360 instead of 330. Finally, the original post is about finding ratios, not single E-series resistors.
– W5VO
Sep 13 at 15:38
• I agree, this code is really neat for finding the nearest value in the E-series. I definitely learnt something. But I think there is still a tiny bug in the formula. It works in the range [1.0 < x <= 4.7]. After that some intermediate values are skipped: see i.stack.imgur.com/73Qol.png Sep 13 at 15:54
• @Velvel I have solved the problem. The culprit was the legacy LOOKUP. I since swapped it with XLOOKUP to round to the nearest E24 value. A 2nd table was added for the E24_Series values to declutter the equations. Sep 14 at 0:35
• Not going to edit this anymore, but if you need to compare the upper and lower resistor values, for the upper resistor value, just change the ROUND to ROUNDUP, and for the lower resistor value change ROUND for ROUNDDOWN and also change the XLOOKUP's match mode parameter from 1 to -1. You can find an example in this 24 step attenuator calculator I made. Sep 17 at 13:04

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.

• Thank you for more illumination on the topic. Though all I needed was the spreadsheet shared above, so one does not have to do the same work, which you people already invested. Sep 6, 2022 at 21:01

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[{#[]/#[], #} &, Permutations[e24values, {2}]];

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

Block[{\$NumberMarks = False},
StringForm["|1|2|3|\n",
NumberForm[#[], 2], #[[2, 1]], #[[2, 2]]]] & /@
({errorF[#]*100, #[]} & /@ 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.