# Excel Formula Calculates Standard 5% Resistor Value

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” (http://electronicdesign.com/article/components/excel-formula-calculates-standard-1-resistor-value.aspx).

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.

andrepetra
on Jun 8, 2012
test
josefclare (not verified)
on Apr 26, 2013

I have to make the choice attached to to thank you might on your veteran assistance I have got many times took pleasure going over your webblog. We're expecting the unique start of predominantly the organization faculty evaluation and also large foundation would not by now total not future to your website. Simply could very well be of a typical help to a few, I am gracious which might help in what I even have discovered came from here.
sell structured settlements

reeti
on May 2, 2013

Before you search the information in the internet, you need to think what your need for the holiday is. Do you want budget holiday? Or do you want holiday with high standard of accommodation? Per the information provided, you can then select and use the camping holiday smartly.
www.traveltrips.co

nicholascage
on May 3, 2013

Another excellent example of innovation, I am happy to find it. There are so several developers working on this segment but this is one of the best innovative idea ever. Thanks for sharing it here. free365betnews.com

##### Commentaries and Blogs
Posted 1 day ago
in Communiqué
###### What’s The Reach Of Your Wireless Router?
Posted 3 days ago
in Joe Desposito's Blog
###### Automotive-Safety Design Gears Up For 65nm ARM Cortex MCU
Posted 4 days ago
in London Calling
##### Forums

make the right choice..."