Re: Excel resistor tolerance calculator
From: The Phantom (phantom_at_aol.com)
Date: 09/04/04
- Next message: Guy Macon: "Re: 3 Single Phase Inverters Star Connected"
- Previous message: Rich Grise: "Re: Theoretical limit for loud music??"
- Messages sorted by: [ date ] [ thread ]
Date: 4 Sep 2004 17:17:23 -0500
On 16 Aug 2004 09:07:58 -0700, u035m4i02@sneakemail.com (Jon) wrote:
>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.
- Next message: Guy Macon: "Re: 3 Single Phase Inverters Star Connected"
- Previous message: Rich Grise: "Re: Theoretical limit for loud music??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
