Rounding Component Values to Various EIA Tables Using Excel(TM) (DS4) 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 do we need for each decade, and how do we find those significant digits? Well, if we know the manufacturing tolerance T, we can solve for those using: (1+T)*10^(m/N) = 10^([m+1]/N) [Eq. 1] where m and N are both integers and N is positive. N represents the number of values we need before repeating the significant digits for the next decade, and 0 <= m <= N. Solving for N gives: N = INT(1/LOG(1+T)) [Eq. 2] 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.3] 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, 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.4] 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 < (high_guess + low_guess)/2, low_guess, high_guess) See the attached zipped 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. 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: v.1.0 Initial release. v.1.01 Minor text changes. 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.2 added explicit formulas for 1% and 5% tolerances. Questions or feedback? E-mail me at jpbedinger@hotmail.com (c)2006 John-Paul Bedinger. All rights reserved. Revision: 1.2 Do not duplicate, distribute, or modify without my expressed written permission. Disclaimer: The author is not responsible for any damages resulting from the content or application of this document. Use at your own risk. |