Excel Formula Calculates Standard 5% Resistor Value

>> 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.