Rounding
Component Values to Various EIA Tables Using Microsoft Excel™ (DS4)
by John-Paul Bedinger
The idea behind the EIA
component value tables is to make the significant digits repeat over and over
with each decade for a given manufacturing tolerance. For instance, 0.11 ohms,
1.1 ohms, and 11 ohms all share the same significant digits “11”. In the EIA-24
(5% tolerance) table, the next value after 1.1 would be 1.2, which is about 5%
greater than 1.1. Similarly, 1.3 is about 5% greater than 1.2, and so on. So,
how many total different values N do we need
for each decade, and how do we find those significant digits? Well, if we know
the manufacturing tolerance T, we first note that the maximum
value on any component’s listed value Vn
should equal the minimum value for the next nominal value in the table Vn+1:
(1+T)·Vn = (1−T)·Vn+1 [Eq. 1]
To get N values over a
decade, that means the next value would have to be the Nth root of
10 times the current value, or:
Vn+1
= 101/N·Vn [Eq. 2]
Plugging equation 1 into
equation 2 gives:
(1+T)·Vn = (1−T)·
101/N·Vn
(1+T) = (1−T)· 101/N
(1+T)/(1-T) = 101/N
Log((1+T)/(1-T)) = 1/N
Solving for N gives:
N = INT(Log((1+T)/(1-T))^-1) [Eq. 3]
INT() is the integer rounding function, which is necessary
to make the table have an integer number of entries. Solving for N given T= 5%
yields N=24. This is the meaning of the table titles like “EIA-24” for 5%
tolerance components. You can extend this idea for the 1% (N=96) and
0.1%(N=192) tables. To find the significant digits for each value in the table,
compute:
V(m) = 10^(m/N) m =
0,1,2...N-1
[Eq.4]
Next, round V(m) an
appropriate number of significant figures. (2 for EIA24, 3 for EIA48 and 96).
As nice as this is, it turns out for the 5% and 10% tables the rounding was not
done quite right [Ed Note: See: E series of preferred numbers - Wikipedia for more information here], so we have to use a special “corrector” formula to
generate the actual table values from the “correct” table values as computed
above, or “correct” the value of m somehow to get the same result. The
following Excel format corrector function F(m) was found by comparing the
actual table values to the predicted values by the procedure without the
corrector, and then empirically fitting (for compactness) a couple of Gaussian
impulse functions on the error. These impulses are then added to the exponent
value. The idea is to activate correction on m for EIA tables of N=24 or less
using as few characters as possible:
F(m) =
IF(N<48,1.1^-((11-m)^2+8)-8^-((22-m)^2+1)+m,m)
[Eq.5]
To make a formula for
Excel or other spreadsheet to round a given value to an EIA equivalent, use the
following formula design procedure:
Low_guess procedure:
1. Scale given_value A between 0 and 10: A/10^INT(LOG(A)) = C
2. Convert C to a low
guess of m based on a given EIA table B(up to 96):
INT(B*LOG(C)) = D
3. Do corrector function
F(D) for given B: IF(B<48,1.1^-((11-D)^2+8)-8^-((22-D)^2+1)+D,D) = E
4. Convert E to a
component value: 10^(E/B) = F
5. Round value E for given
B: ROUND(F, IF(B>24,2,1)) = G
6. Rescale G back to
original given decade: G*10^(INT(LOG(A))) = H = low_guess
High_guess procedure: as
for low_guess, but add 1 to C on step 2.
Choose best guess:
7. EIA_rounded
= IF(given_value < (low_guess + high_guess)/2, low_guess, high_guess)
See the attached
Excel spreadsheet
for this process broken down into steps by column. Cell P1 is the total
combined “universal” EIA formula based on input value in A1, and table to use
in B1.
For those just
interested in 1% and 5% tolerance component value rounding, see the following
formulas I made. Be sure to remove
any line feeds in Excel after cutting and pasting. Also note that the
reference cell for the 5% values is A5, not A1!
For 1% tolerance
(EIA-96) on some value in cell A1:
=IF(A1<(ROUND(10^(INT(96*LOG(A1))/96),2-INT(LOG(A1)))+ROUND(10^(INT(96*LOG(A1)+1)/96),2-INT(LOG(A1))))/2,
ROUND(10^(INT(96*LOG(A1))/96),2-INT(LOG(A1))),ROUND(10^(INT(96*LOG(A1)+1)/96),2-INT(LOG(A1))))
For 5% tolerance
(EIA-24) on some value in cell A5:
=IF(A5<(ROUND(10^((1.1^-((11-INT(24*LOG(A5/10^INT(LOG(A5)))))^2+8)-8^-((22-INT(24*LOG(A5/10^INT(LOG(A5)))))^2+1)
+INT(24*LOG(A5/10^INT(LOG(A5)))))/24),1)*10^(INT(LOG(A5)))+ROUND(10^((1.1^-((11-INT(24*LOG(A5/10^INT(LOG(A5)))+1))^2
+8)-8^-((22-INT(24*LOG(A5/10^INT(LOG(A5)))+1))^2+1)+INT(24*LOG(A5/10^INT(LOG(A5)))+1))/24),1)*10^(INT(LOG(A5))))/2,
ROUND(10^((1.1^-((11-INT(24*LOG(A5/10^INT(LOG(A5)))))^2+8)-8^-((22-INT(24*LOG(A5/10^INT(LOG(A5)))))^2+1)
+INT(24*LOG(A5/10^INT(LOG(A5)))))/24),1)*10^(INT(LOG(A5))),ROUND(10^((1.1^-((11-INT(24*LOG(A5/10^INT(LOG(A5)))+1))^2
+8)-8^-((22-INT(24*LOG(A5/10^INT(LOG(A5)))+1))^2+1)+INT(24*LOG(A5/10^INT(LOG(A5)))+1))/24),1)*10^(INT(LOG(A5))))
Change Log:
v1.3 Removed some legal
rambling at the bottom of this change log – that’s all in the About This Site
and General Disclaimer now. Fixed derivation of N values from tolerance. Added
a reference to the Wikipedia page on the topic, a reminder that cell A5 is for
the 5% resistors, and made some minor formatting changes.
v.1.2 added explicit
formulas for 1% and 5% tolerances.
v.1.1 changed formula in
step 5 to IF(B>24,2,1) = G, and in attached zipped excel spreadsheet to fix
EIA-48 rounding.
v.1.01 Minor text changes.
v.1.0 Initial release.