Seeking advice on a algorithm to calculate which resistors needed to result in xOhms

Hello.

I want to update my excel inventory file, I have a page for resistors, one for caps etc.

My resistor page needs to be more fun but also there are times when a 208,4kΩ resistor can't be replaced by a 200kΩ resistor without making a loss in accuracy or precision that is not acceptable when you are trying to design a precision device.

Although perhaps rare there are those occasions, but if you don't like that motivation then I have a second one for this thread.
Because it's fun.

What I want to do is to input a value, lets say 208,4kΩ and then the algorithm shall calculate the combinations of the resistors in my inventory that gets as close as possible to that value.

Forget I mentioned Excel, I just want a general algorithm that I can implement in what way I end up seeing best.

I have been thinking about it and I have seen a website which have a calculator that does this but uses hole series of resistor values, it outputs a list of combinations. It uses series-, parallel- & series/parallel-combinations if I recall correctly but I have run into a thought wall.

I got so far as to consider to compile a "library", in my inventory the resistors are labelled R1, R2, R3 and so on.
So first I take R1 and calculate the combination of and so on, then
R1//R1, R1//R2, R1//R3, R1//R4, R1//R5, etc
R2//R1, R2//R2, R2//R3, R2//R4, R2//R5, etc
R3//R1, R3//R2, R3//R3, R2//R4, R2//R5, etc
etc
etc
etc

Then... ... ... ... well... help.
I don't know how to proceed apart from continue generating more series of values somewhat like the first but that I foresee will go nowhere.

Do anyone have any good idea, or idea for how to realize this algorithm?

Regards

Perhaps there is a similar free spreadsheet template on the internet?

Although there's nothing wrong with wanting to be precise, you are bound to get a few 5 and 10 percent tolerance resistors in your collection. You will have to measure each one precisely, so that you have its correct value in precise calculations. How accurate is your meter? Can your spreadsheet handle wide ranges of minimum/ maximum values?

In fact it becomes enjoyable to have a large assortment of parts, so numerous that you are just about able to keep them organized in labelled compartments. When I have wanted to build a project on a whim, I could find the components I needed. It's okay if I had to search a while.

Certainly by all means, continue to explore the wide range of fun aspects in the electronics field.

You can do it mathematically but that will tell you the exact parallel value needed when the first one is known. I think you are looking for a generic system that picks the best combination of ones already present in the spreadsheet.

Firstly - I must point out that I use LibreOffice under Linux but I think Excel has similar features.

I'm calling Rx the resistance you want to find, R1 and R2 the ones to select.
Making the assumption that you probably want the values to be as close to each other as possible so power is more likely to be equally dissipated:
1. find half of Rx as a starting point.
2. find the nearest value in your table to it, call it Rh (resistance halved). This is your first guess at R1.
3. use the formula (Rh * R2)/(Rh + R2) to find the value for R2
4. use "goal seeking" with R2 as the target and Rx as the goal. This will tell you the optimal value for R2.
5. note the error between Rx and (R1 * R2)/(R1 + R2).
6. go back to step 2 but use the next nearest value in your list and try again.

Look for the combination with lowest error, these will be the values for R1 and R2 that most closely match Rx.

Alternatively, you can use the 'brute force' method and apply the formula to every entry in the table then pick the combination with least error. The drawback to that method is it may take longer and produce results which are mathematically closer but have less practical values, for example 1K in parallel with 10M.

Brian.

Acctually I have not a single resistor above 1% tolerance, though I have not been collecting for long and I have bought small quantityes of 0,1% and 1% resistors when Elfa distrelect has had sales(as they always have in some manner or other, old stock being discontinued and stuff. I tend to check there from time to time since once in a while you can make real bargins. I got a P-touch 2030 label print machine that has a keyboard and user interface and prints labels that has a paper on the back which is peild of to uncover a glue surface. all for something like 10 bucks. that was insane)

It will take me some more time to see the suggestions through but know I feel as I can manage to realize some form of what I want. Thanks you very much.

Its probably less useful to find an exact value, than to find an exact ratio.

Ratios are particularly useful for voltage dividers and gain setting of op amps for example.

I made up a wall chart matrix with all the E24 resistor values (one decade only).
24 horizontal values and 24 vertical values with all the associated ratios on the chart.

Suppose I needed to find two resistors with a ratio of around 3.74:1
I can see at a glance that:

82/22 = 3.727
56/15 = 3.733
75/20 = 3.750
68/18 = 3.778

betwixt said:

You can do it mathematically but that will tell you the exact parallel value needed when the first one is known.

Click to expand...

The reverse problem is more interesting. You give the software a set of numbers corresponding to the resistor values you have in hand. We always assume that you have multiple copies of identical values.

You ask the software that given a target value RX, find at most three (or four) resistors from the set in hand that can be combined in series/parallel such that the combination is closest to RX.

This problem has a mathematical solution (minimax type) but I have forgotten that.

David_ said:

What I want to do is to input a value, lets say 208,4kΩ and then the algorithm shall calculate the combinations of the resistors in my inventory that gets as close as possible to that value.

Click to expand...

Its not that difficult.
200K in series with 8K2 will get you pretty close.
If you use 1% resistors you could be up to 2K out anyway.

If parallel is more your style, then go to the next highest common value like 220K, then work out what really high value resistor you need to place across it to pull it back to 208.4K.
That would be 3.9meg.

If you work it out that comes to 208.2524K (in theory)
But as before, if your resistors are 1%. it could still be up to 2K in error.

Hi,

to avoid too many not useful combinations you may use limits:

With parallelling resistors:
One resistor should be in the range of R(target) ... 2 x R(target), wich is a narrow range
(then the other is in the range of 2 x R(target) ... inifnte, wich is a wide range)

With serial connection:
One resistor should be in the range of R(target)/2 ... R(target), wich is a narrow range)
(then the other resistor is also in the range of 0 ... R(target)/2, wich is a wide range )

Just seeking in the narrow range limits the number of possible combinations...and calculations.

***
Just for fun: what about combining three resistors.

Klaus

David_ said:

... there are times when a 208,4kΩ resistor can't be replaced by a 200kΩ resistor without making a loss in accuracy or precision that is not acceptable when you are trying to design a precision device.

Although perhaps rare there are those occasions, but if you don't like that motivation then I have a second one for this thread.
Because it's fun.

What I want to do is to input a value, lets say 208,4kΩ and then the algorithm shall calculate the combinations of the resistors in my inventory that gets as close as possible to that value.

Forget I mentioned Excel, I just want a general algorithm that I can implement in what way I end up seeing best.

I have been thinking about it and I have seen a website which have a calculator that does this but uses hole series of resistor values, it outputs a list of combinations. It uses series-, parallel- & series/parallel-combinations if I recall correctly but I have run into a thought wall.

I got so far as to consider to compile a "library", in my inventory the resistors are labelled R1, R2, R3 and so on.
So first I take R1 and calculate the combination of and so on, then
R1//R1, R1//R2, R1//R3, R1//R4, R1//R5, etc
R2//R1, R2//R2, R2//R3, R2//R4, R2//R5, etc
R3//R1, R3//R2, R3//R3, R2//R4, R2//R5, etc
etc
etc
etc

Then... ... ... ... well... help.
I don't know how to proceed apart from continue generating more series of values somewhat like the first but that I foresee will go nowhere.

Do anyone have any good idea, or idea for how to realize this algorithm?

Click to expand...

I recently was solving this same problem. So far I settled on a relatively simple exhaustive search. I.e., start from a list of resistances, compute all values possible with 2 resistors, then 3, etc.. So the algorithm is really simple - just make a list of values available with each number of resistors, and combine them further to make the next list.

It works well enough, except when the stock list and the number of resistors to combine are both large, which results in combinatorial explosion. Not sure how well this would work in Excel. Perhaps it could be OK for 2 resistors, or a small stock.

Here are some possibilities for making 208.4k out of a stock ranging from 1 Ohm to 1 MOhm:

• Using 3 E3 resistors: 10k + 100k + 100k = 210k (0.768% error)
• Using 4 E3 resistors: 22k + (220k || (220k + 1M)) ≈ 208.389k (0.005% error)
• Using 3 E6 resistors: 680k || (150k + 150k) ≈ 208.163k (0.114% error)
• Using 4 E6 resistors: 680k || (470 + 150k + 150k) ≈ 208.389k (0.005% error)
• Using 2 E12 resistors: 330k || 560k ≈ 207.640k (0.364% error)
• Using 3 E12 resistors: 330k || (5.6k + 560k) ≈ 208.406k (0.003% error)

(Of course the accuracy of the stock will ultimately limit the accuracy of the eventual network, the final network has to be measured before use, etc.).

The list of all possibilities can be enormous. For example, when computing the last choice in the list above (3 E12 resistors), 1,488,543 networks and 480,558 unique resistance values were searched. So I am not sure about the idea of putting them all in a list.

You're welcome to try my tool: http://kirr.homeunix.org/electronics/resistor-network-finder/. Although I used whole series in the calculation above, the tool supports configurable stock, so you can specify the values that you have.

BTW I see it's an old thread. Sorry if this is not needed anymore.