Excel resistor tolerance calculator

[Jon]

I am sure this exists, but can't seem to find it online. I want the
formulas to find the nearest 10%, 1%, etc. resistor value for a given
desired value in an Excel spreadsheet. Here is a version for 1%,
which is apparently more complicated than it needs to be:

http://www.elecdesign.com/Articles/ArticleID/1480/1480.html

In the associated comments there is a shorter version that works
equally well (although they give different results for values right on
the edge, like 1.01, which maps to 1.00 in one and 1.02 in the other,
otherwise give exactly the same results):

http://www.elecdesign.com/articles/index.cfm?Action=Comments&ArticleID=1480

And others have written in saying they have even shorter formulas and
universal formulas. Anyone know the better ones?

Jon

 

[Rich Grise]

I am sure this exists, but can't seem to find it online. I want the
formulas to find the nearest 10%, 1%, etc. resistor value for a given
desired value in an Excel spreadsheet. Here is a version for 1%,
which is apparently more complicated than it needs to be:

http://www.elecdesign.com/Articles/ArticleID/1480/1480.html

In the associated comments there is a shorter version that works
equally well (although they give different results for values right on
the edge, like 1.01, which maps to 1.00 in one and 1.02 in the other,
otherwise give exactly the same results):

http://www.elecdesign.com/articles/index.cfm?Action=Comments&ArticleID=1480

And others have written in saying they have even shorter formulas and
universal formulas. Anyone know the better ones?

Does this help?
http://www.rfcafe.com/references/electrical/resistor_values.htm

That's about the first hit when I put "standard resistor values" no quotes,
in http://www.google.com

Good Luck!
Rich

 

[The Phantom]

I am sure this exists, but can't seem to find it online. I want the
formulas to find the nearest 10%, 1%, etc. resistor value for a given
desired value in an Excel spreadsheet. Here is a version for 1%,
which is apparently more complicated than it needs to be:

http://www.elecdesign.com/Articles/ArticleID/1480/1480.html

In the associated comments there is a shorter version that works
equally well (although they give different results for values right on
the edge, like 1.01, which maps to 1.00 in one and 1.02 in the other,
otherwise give exactly the same results):

http://www.elecdesign.com/articles/index.cfm?Action=Comments&ArticleID=1480

And others have written in saying they have even shorter formulas and
universal formulas. Anyone know the better ones?

Jon

Quite a few years ago, more than 20, somebody published the
following little Basic routine in one of the free magazines. I have
lost the original article and cannot give proper credit. This routine
accepts a resistor value that perhaps came from an exact calculation,
and a tolerance, and returns the nearest standard value from that
tolerance range.

10 INPUT "R, TOLERANCE? ";R,T
20 Z4=.0119926*INT(1+1.5*T+.004*T^2)
30 Z3=INT(LOG10(R)-INT(2.2-3*Z4))
40 R=R/(10^Z3)
50 FOR I=1 TO 2
60 Z(I)=INT(EXP(Z4*(INT(LN(R)/Z4)+I-1))+.5)
70 Z5=.0000188*Z(I)^3-.00335*Z(I)^2+.164*Z(I)-1.284
80 Z(I)=Z(I)+INT(Z5*INT(3*Z4+.8))
90 IF Z(I)=919 THEN Z(I)=920
100 NEXT I
110 R=10^Z3*Z(INT(R/SQR(Z(1)*Z(2)))+.5)
120 PRINT R

Notice that both LOG10 and LN are used. Some Basic's may not have
both. Make the appropriate changes.

Line 90 is there to correct a peculiarity of the E192 series of
resistor values. It seems that when the original series was created,
a slight rounding error occurred. The expression for the 186th value
in the series would be 10^(185/192) which gives 9.19478686, which
rounded to 3 digits would give 9.19; but the official value is 9.20.
Since the routine above would give the mathematically correct value
which would not be a standard part, line 90 is needed.

This routines works for 20%, 10%, 5%, 2%, 1%, .5%, .25%, and .1%
resistor series.