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.