The attached Excel file will show all possible resistor combination that (within a specifiable margin) fits the given/requested/wanted/mystery value.
There is one .xlsx file (no automation) and one .xlsm file (that contains automatic filtering) in the attached .zip archive (but you may not want to run 'foreign' macro's on your system, in that case you need to do the filtering by manual excitation of the keyboard and mouse).
The manual
1) Set the wanted value in cell A4
2) Set the wanted range (E96, E48, ... E6) in B4
3) Set the precision (margin that makes the calculated value shown) in C4
4) Set the precision (margin that makes the calculated value adored) in C5
5) Goto; Data -> Filter -> Advanced -> List Range = $J$5:$J$774 and then check 'Unique Records Only'
6) Done
Post #8 has an updated version http://www.diyaudio.com/forums/soft...-parallel-resistor-solutions.html#post5008844
Post #9 has the macro-code as text http://www.diyaudio.com/forums/soft...-parallel-resistor-solutions.html#post5008855
Post #15 has the latest version http://www.diyaudio.com/forums/soft...arallel-resistor-solutions-2.html#post5014775
There is one .xlsx file (no automation) and one .xlsm file (that contains automatic filtering) in the attached .zip archive (but you may not want to run 'foreign' macro's on your system, in that case you need to do the filtering by manual excitation of the keyboard and mouse).
The manual
1) Set the wanted value in cell A4
2) Set the wanted range (E96, E48, ... E6) in B4
3) Set the precision (margin that makes the calculated value shown) in C4
4) Set the precision (margin that makes the calculated value adored) in C5
5) Goto; Data -> Filter -> Advanced -> List Range = $J$5:$J$774 and then check 'Unique Records Only'
6) Done
Post #8 has an updated version http://www.diyaudio.com/forums/soft...-parallel-resistor-solutions.html#post5008844
Post #9 has the macro-code as text http://www.diyaudio.com/forums/soft...-parallel-resistor-solutions.html#post5008855
Post #15 has the latest version http://www.diyaudio.com/forums/soft...arallel-resistor-solutions-2.html#post5014775
Attachments
Last edited:
I tried it out but only have OpenOffice Calc and tried the .xlsx file.
I assumed that C4 is the component tolerance and C5 is for the desired tolerance.
I didn't understand step 5) I guess that in Excel it shows something but Open Office seems to fail.
I have done something simpler to give options to select two series connected resistors to get closest to a target value.
I assumed that C4 is the component tolerance and C5 is for the desired tolerance.
I didn't understand step 5) I guess that in Excel it shows something but Open Office seems to fail.
I have done something simpler to give options to select two series connected resistors to get closest to a target value.
PChi, ghk,
I'm sorry but the spreadsheet is created for/using Microsoft's Excel (it is what I have), there are no guaranties that it will operate in any other environment.
PChi, Step 5 sets/activates a filter that removes all duplicated rows, this will make reading and using the results more easily than having to find them in all rows.
It seems [from your messages] that OpenOffice works better (is better equipped to make use of) with the Excel spreadsheet, if you get to step 5 then you can not make (as it seems) use of the filter [to remove non-unique] rows. There should be an, other than the Excel, set of commands to accomplish this in OpenOffice. The [literal] effect of the filter is to 'Remove all non-unique rows in the range J5:J774' (I hope that helps to find an alternative solution).
I will update the uploaded version to my 'current' version (slightly updated, mostly the version that uses macro-programming). And separate from the Excel file I will upload the macro-code as plain text, if any one will/would make a operable LibreOffice or/and OpenOffice version from that, then I will (when posted here) odd these to the top (first) message of this thread.
P.s. The 'macro' version contains some 'non-essential' code, used to change the color of cell-C4 while calculating, maybe this code is a part of the problem. For this reason I will add a second 'macro'-version of the spreadsheet that does not contain this code. Let me know if this helps.
I'm sorry but the spreadsheet is created for/using Microsoft's Excel (it is what I have), there are no guaranties that it will operate in any other environment.
PChi, Step 5 sets/activates a filter that removes all duplicated rows, this will make reading and using the results more easily than having to find them in all rows.
It seems [from your messages] that OpenOffice works better (is better equipped to make use of) with the Excel spreadsheet, if you get to step 5 then you can not make (as it seems) use of the filter [to remove non-unique] rows. There should be an, other than the Excel, set of commands to accomplish this in OpenOffice. The [literal] effect of the filter is to 'Remove all non-unique rows in the range J5:J774' (I hope that helps to find an alternative solution).
I will update the uploaded version to my 'current' version (slightly updated, mostly the version that uses macro-programming). And separate from the Excel file I will upload the macro-code as plain text, if any one will/would make a operable LibreOffice or/and OpenOffice version from that, then I will (when posted here) odd these to the top (first) message of this thread.
P.s. The 'macro' version contains some 'non-essential' code, used to change the color of cell-C4 while calculating, maybe this code is a part of the problem. For this reason I will add a second 'macro'-version of the spreadsheet that does not contain this code. Let me know if this helps.
Last edited:
I use a calculator to find the required paralleled combination to hit my resistance target.Questions:
1) Is any one using the spreadsheet?
2) Is it any good
3) Should it be improved?
4) What improvements do you wish for?
5) Or maybe, did it fail completely?
6) You do not use resistors (or capacitors)
7) You ask: What is a resistor?
I did years ago print out a table of all the E24 combinations. This look up table was fairly quick to use. Far quicker than starting some software, but I found hand calculation is virtually as easy.
Series combinations don't need a calculator.
Now let's see what a 2k4 in series with a 130r||(10VR+1k8) gives us?
2521.24ohms to 2528.58ohms. Just fine for adjusting a Twin T tuned to 10kHz.
Andrew, the spreadsheet is not to calculate filter values or solve resistor network puzzle's, the spreadsheet is to solve the question 'I need a 60r [or what ever] resistor, how to make it from 2 others [that I may have in stock]' and then to show me all possible solutions in a given margin(precision) even for existing E<x> value's (is useful if that one value is out of stock).
While this could be done with an calculator, it may be faster to use a 'software'
While this could be done with an calculator, it may be faster to use a 'software'
Attachments
Updated version,
The zip-file contains 3 versions
1) FdWs E96, E48 and E24 Resistor Table and Calculator; NoMacro.xlsx
Contains no macro's, filtering the row's is DIY
2) FdWs E96, E48 and E24 Resistor Table and Calculator; Simple.xlsm
FdWs E96, E48 and E24 Resistor Table and Calculator; Simple.txt
Automated and may (or not) work in other spreadsheet programs (other than Excel)
3) FdWs E96, E48 and E24 Resistor Table and Calculator; Full.xlsm
FdWs E96, E48 and E24 Resistor Table and Calculator; Full.txt
Automated and signals busy (by changing the color of cell A4) also moves the cursor (selected cell) back to A4 after re-calculating.
The zip-file contains 3 versions
1) FdWs E96, E48 and E24 Resistor Table and Calculator; NoMacro.xlsx
Contains no macro's, filtering the row's is DIY
2) FdWs E96, E48 and E24 Resistor Table and Calculator; Simple.xlsm
FdWs E96, E48 and E24 Resistor Table and Calculator; Simple.txt
Automated and may (or not) work in other spreadsheet programs (other than Excel)
3) FdWs E96, E48 and E24 Resistor Table and Calculator; Full.xlsm
FdWs E96, E48 and E24 Resistor Table and Calculator; Full.txt
Automated and signals busy (by changing the color of cell A4) also moves the cursor (selected cell) back to A4 after re-calculating.
Attachments
Last edited:
I downloaded your first .xlsm version - came in just handy for a notch filter that I'm calculating.
For the first of the two resistances that I need (5950R and 13850R) I got the same combination (10K par 14K7, I needed four tries with a calculator), and for the second one it found a more accurate solution.
A very useful tool, thankyou very much for sharing!
Regards,
Braca
For the first of the two resistances that I need (5950R and 13850R) I got the same combination (10K par 14K7, I needed four tries with a calculator), and for the second one it found a more accurate solution.
A very useful tool, thankyou very much for sharing!
Regards,
Braca
Thanks for that report Jim, maybe some more people will share there experience with version V2.
Last edited:
This one is the best ever (up until now ) it is more accurate, it is faster, it fixes a small error for values below 0.1 Ohm, it no longer supports the version without automation, it shows the results in a single column table, it fixes a small error in one of the tables (actually the error was copied from a wikki page that lists E<x> values) and finally it is free
There was a small error when calculating the average between odd-E<6|12|24>n values (I think no one would have noticed it), this has been solved by changing the 'averaging' rules. P.s. odd-E<6|12|24>n are the values that need correcting like 33, 47 (see attached pdf note the values in red) and some others.
You may ask, why are there two E24 ranges? The answer is simple, I do always buy E96 values for my stock, but stocking a complete set of E96 values from 0.1 up to 10Meg Ohm is a bit to much (for me). For this reason I stock a E24 subset of values of E96 'members', that range is called [here] E24x. The 'normal' E ranges supported are called E96n...E6n.
The current version is now V4
) it is more accurate, it is faster, it fixes a small error for values below 0.1 Ohm, it no longer supports the version without automation, it shows the results in a single column table, it fixes a small error in one of the tables (actually the error was copied from a wikki page that lists E<x> values) and finally it is free There was a small error when calculating the average between odd-E<6|12|24>n values (I think no one would have noticed it), this has been solved by changing the 'averaging' rules. P.s. odd-E<6|12|24>n are the values that need correcting like 33, 47 (see attached pdf note the values in red) and some others.
You may ask, why are there two E24 ranges? The answer is simple, I do always buy E96 values for my stock, but stocking a complete set of E96 values from 0.1 up to 10Meg Ohm is a bit to much (for me). For this reason I stock a E24 subset of values of E96 'members', that range is called [here] E24x. The 'normal' E ranges supported are called E96n...E6n.
The current version is now V4
Attachments
Last edited:
Hi FdW,
This XLS is just what i'm looking for!
An amazing piece of work & so far beyond my ability to Excel bash. however...
V4 causes Excel to hang when i open the file.
(think i'm running office 2016 but i've hung Excel & need to reboot the PC & thus loose this msg...) = there is a hole in my bucket Dear Lisa!
any ideas?
Cheers!
DnC
:-j
[p.s. your personal messages folder won't accept anymore PM's]
This XLS is just what i'm looking for!
An amazing piece of work & so far beyond my ability to Excel bash. however...
V4 causes Excel to hang when i open the file.
(think i'm running office 2016 but i've hung Excel & need to reboot the PC & thus loose this msg...) = there is a hole in my bucket Dear Lisa!
any ideas?
Cheers!
DnC
:-j
[p.s. your personal messages folder won't accept anymore PM's]
- Status
- This old topic is closed. If you want to reopen this topic, contact a moderator using the "Report Post" button.