Electronicdesign 29095 Promo Ifdvol1 No3 2
Electronicdesign 29095 Promo Ifdvol1 No3 2
Electronicdesign 29095 Promo Ifdvol1 No3 2
Electronicdesign 29095 Promo Ifdvol1 No3 2
Electronicdesign 29095 Promo Ifdvol1 No3 2

Excel Formula Calculates Standard 5% Resistor Value

May 24, 2012
Article provides a link to a downloadable Excel spreadsheet for determining “standard” 5%-precision values of components such as resistors. There is also a cut-and-paste listing.

>> Electronic Design Resources
.. >> Library: Article Series
.. .. >> Series: Ideas for Design
.. .. .. >> Ideas for Design Vol. 1

Download this article as a PDF file

When using a spreadsheet to calculate circuit values, determining “standard” component values to be used in subsequent calculations can produce more accurate results. This approach will also result in real-life solutions to your circuit designs. When 5% resistors will suffice, the Excel solution below will provide values that can directly be used for a material list.

The general formula to calculate standard resistor values is given below. The results are then rounded to the proper number of significant figures (3 for 1% and 2%, 2 for 5%):

r = d*10 i/N | i = 0, 1, 2, to N-1

where d = decade multiplier (0.1, 1, 10, etc.) and N = number of values per decade. For the 1% resistor, the value of N = 96, for 2% N = 48, and for 5% N = 24.

This formula is true for 1% and 2% standard resistors, but the 5% resistors do not track exactly. In fact, a full one-third of the 5% “preferred” values deviate from the formula. Therefore, the solution to this problem is not elegant like the Excel solution for the 1% resistor described in “Excel Formula Calculates Standard 1% Resistor Values”.

The simple Excel formula below determines the nearest 5% standard resistor value by comparison without using a lookup table or a macro. A preliminary calculation is needed to determine the decade multiplier.

Type or paste the preliminary calculation formula below into cell B1, then type or paste the final calculation formula into any cell other than A1. The formula will calculate the nearest 5% resistor for the value in cell A1. Be sure when copying the formula to other cells that both preliminary and final calculations are replicated. The preliminary calculations can be hidden by collapsing the column.

Preliminary calculation in cell B1:

=10^INT(LOG(A1))

Final calculation in any cell:

=IF(A1=0,0,
IF((A1/B1)<1.05,1*B1,
IF((A1/B1)<1.15,1.1*B1,
IF((A1/B1)<1.25,1.2*B1,
IF((A1/B1)<1.4,1.3*B1,
IF((A1/B1)<1.55,1.5*B1,
IF((A1/B1)<1.7,1.6*B1,0)))))))+
IF((A1/B1)<1.7,0,
IF((A1/B1)<1.9,1.8*B1,
IF((A1/B1)<2.1,2*B1,
IF((A1/B1)<2.3,2.2*B1,
IF((A1/B1)<2.55,2.4*B1,
IF((A1/B1)<2.85,2.7*B1,
IF((A1/B1)<3.15,3*B1,0)))))))+
IF((A1/B1)<3.15,0,
IF((A1/B1)<3.45,3.3*B1,
IF((A1/B1)<3.75,3.6*B1,
IF((A1/B1)<4.1,3.9*B1,
IF((A1/B1)<4.5,4.3*B1,
IF((A1/B1)<4.9,4.7*B1,
IF((A1/B1)<5.35,5.1*B1,0)))))))+
IF((A1/B1)<5.35,0,
IF((A1/B1)<5.9,5.6*B1,
IF((A1/B1)<6.5,6.2*B1,
IF((A1/B1)<7.15,6.8*B1,
IF((A1/B1)<7.85,7.5*B1,
IF((A1/B1)<8.65,8.2*B1,
IF((A1/B1)<9.55,9.1*B1,10*B1)))))))

Note: Type this formula into the spreadsheet cell as a continuous entry without carriage returns.

Sponsored Recommendations

Board-Mount DC/DC Converters in Medical Applications

March 27, 2024
AC/DC or board-mount DC/DC converters provide power for medical devices. This article explains why isolation might be needed and which safety standards apply.

Use Rugged Multiband Antennas to Solve the Mobile Connectivity Challenge

March 27, 2024
Selecting and using antennas for mobile applications requires attention to electrical, mechanical, and environmental characteristics: TE modules can help.

Voltage- and Current-Mode Control for PWM Signal Generation in DC-to-DC Switching Regulators

March 27, 2024
Learn voltage- and current-mode control technique for PWM-signal generation in switching-voltage regulators and where each application is best suited.

Highly Integrated 20A Digital Power Module for High Current Applications

March 20, 2024
Renesas latest power module delivers the highest efficiency (up to 94% peak) and fast time-to-market solution in an extremely small footprint. The RRM12120 is ideal for space...

Comments

To join the conversation, and become an exclusive member of Electronic Design, create an account today!