This article presents a simple spreadsheet for designing resistive voltage dividers using standard 1% resistor values. The application note describes how to use the spreadsheet and provides a link to download the spreadsheet file.
The resistive voltage-divider is a basic circuit that is taught in every introductory electronics course. But choosing values from standard components is not so easy. If you have ever found yourself in front of a resistor kit while punching values into your calculator, then this article will help you.
The problem is that typical resistor kits do not come with every value.This article demonstrates a small spreadsheet that provides a table of 1% resistor values, and makes it easy to find a needed ratio using two available resistors.
The standard 1% resistor values are logarithmically spaced every 1/96th of a decade. Figure 1 shows a spreadsheet table of 1% resistor values, rounded to three significant digits. The formula in cell B5 is =10^((B$4+$A5)/96), which is copied to B5 through G20. The yellow highlighted values are commonly available in resistor kits. Those yellow highlights tell you at a glance that the typical kit contains slightly more than one quarter of the values.
Figure 2 shows a typical application for a resistive divider: external feedback for a voltage regulator, such as the MAX8560 4MHz step-down converter. The divider ratio of R2/(R1+R2) sets the regulator's output voltage. The MAX8560 has a feedback threshold of 0.6V at FB. In this example, a 1.5V output is desired.
To calculate values for R1 and R2, enter the regulator's desired output voltage (voltage at the top of the divider) in cell B3 and the regulator's feedback threshold (voltage at the divider midpoint) in cell C3. Then, the spreadsheet formula in cell D3 is =96*LOG(B3/C3-1), which returns a value of +16.9 steps for this example. This result is the number of 1% resistor value steps that separate R1 from R2. Therefore, if you use R2=100kΩ (cell B5), you would move +17 steps down the list to R1=150kΩ (cell C6). This would be a good choice because both resistors are highlighted yellow (i.e., commonly available).
You can see very quickly from the spreadsheet table that R2=110kΩ is not a good choice for this example because R1=165kΩ is not commonly available. You can just as quickly identify an exhaustive list of commonly available resistor values that would be suitable: 1.00:1.50, 1.21:1.82, 1.62:2.43, 1.82:2.74, 2.00:3.01, 2.21:3.32, 3.32:4.99, and 4.99:7.50.
When the number of steps, D3, is negative, R1 is less than R2 and you move the opposite direction on the value list. In either direction, you may wrap around the list at 9.76 to 1.00, indicating that you have moved to the next decade of resistors.
The spreadsheet of Figure 1 is available for download in Microsoft Excel format at Resistor Values. It is also available in Pocket Excel format for Pocket PC at Pocket Resistor Values. It is formatted to fit the Pocket PC screen. If you use resistive dividers for purposes other than regulator feedback, you may wish to rename cells B2 and C2 to "Vtop" and "Vmid", respectively.