# Excel Formula Calculates Standard 1%-Resistor Values

The Excel cell formula below calculates the nearest standard 1%-resistor value without using a lookup table or macro. Type or electronically paste the text below into any cell (other than A1). The formula will calculate the nearest 1%-resistor for the value in cell A1. This formula can be copied and replicated to other cells just like any standard Excel cell formula.

A properly constructed spreadsheet used to calculate standard circuit values can produce more accurate designs. When calculating interdependent component values, standard resistor values can be used to calculate subsequent component values. This approach avoids a buildup of tolerance errors. Also, it will sometimes yield a more accurate design than if "perfect" calculated values are converted to nearest standard 1% values following the completion of all other calculations.

Mantissas of the 96-value-per-decade sequence are derived by

r = 10 i/96 where i = 0, 1, 2 to 96

Each mantissa is then rounded to three significant digits, which creates small deviations from perfectly spaced values. The Excel formula calculates the two nearest standard values and tests them with the desired resistor value to select the closer.

=IF(A1>(INT(0.5+100*POWER(10,I
F(96*(LOG(A1)-INT(LOG(A1)))-
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)<0,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)-1,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0))/96)) *
POWER(10,INT(LOG(A1))-2) +
INT(0.5+100*POWER(10,(IF(96*(LO
G(A1)-INT(LOG(A1)))-
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)<0,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)-1,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0))+1)/96)) *
POWER(10,INT(LOG(A1))-2))/2,
INT(0.5+100*POWER(10,(IF(96*(LO
G(A1)-INT(LOG(A1)))-
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)<0,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)-1,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0))+1)/96)) *
POWER(10,INT(LOG(A1))-
2),INT(0.5+100*POWER(10,IF(96*(L
OG(A1)-INT(LOG(A1))) -
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)<0,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0)-1,
ROUND(96*(LOG(A1)-
INT(LOG(A1))),0))/96)) *
POWER(10,INT(LOG(A1))-2))

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

ivicai
on Nov 12, 2010
This work fine too for E96 , E48 and E24

For E96
=ROUND((10^(ROUND(96*LOG(A1),0)/96))/10^INT(LOG((10^(ROUND(96*LOG(A1),0)/96)))),2)*10^INT(LOG((10^(ROUND(96*LOG(A1),0)/96))))

For E48
=ROUND((10^(ROUND(48*LOG(A1),0)/48))/10^INT(LOG((10^(ROUND(48*LOG(A1),0)/48)))),2)*10^INT(LOG((10^(ROUND(48*LOG(A1),0)/48))))

For E24
=ROUND((10^(ROUND(24*LOG(A1),0)/24))/10^INT(LOG((10^(ROUND(24*LOG(A1),0)/24)))),1)*10^INT(LOG((10^(ROUND(24*LOG(A1),0)/24))))

##### Commentaries and Blogs
###### What’s The Reach Of Your Wireless Router?
Posted 1 day ago
in Joe Desposito's Blog
###### Automotive-Safety Design Gears Up For 65nm ARM Cortex MCU
Posted 3 days ago
in London Calling
###### PCIM 2013: Solid state battery is fully implantable
Posted 3 days ago
in Crosstalk
##### Forums

make the right choice..."