Electronic Design

Use Excel To Calculate A-D Level-Shifter Resistor Values

Many times, the need arises to interface single-supply analog-to-digital converters (ADCs) and comparators to real-world signals like 5 V. Of course, it's possible to condition the signal using operational and/or instrumentation amplifiers. But few engineers realize that it's often possible to achieve the level shifting using a resistor network (Fig. 1).

Critics of this technique point out that the resistor network can load the source voltage and cause distortion. We can build this limitation into Microsoft Excel to ensure the resistor values don't overload the input. Another possible concern is that some ADCs won't run properly with a high source impedance, so you would probably have to buffer with a suitable operational amplifier.

To calculate resistor values, we need to use Kirchhoff's Law: The sum of currents into a node is zero. Considering the node at the junction of the three resistors, we can write:

(VIN - VAD)/R1 + (VDD - VAD)/R2 + (VSS - VAD)/R3 = 0


As an example, let us assume VDD = 5 V, VSS = 0 V, VIN = 5 V, and the ADC input must go from 0 to 2.5 V. For VIN = -5 V, we want VAD = 0 V. So we can substitute the values in Equation 1:

(-5/R1) + (5/R2) = 0


For VIN = +5 V, we want the input to be 2.5 V, so substituting in Equation 1:

(2.5/R1) + (2.5/R2) - (2.5/R3) = 0


We have two equations with three unknowns, leaving one degree of freedom. Now we can go ahead and solve this. If we reduce the generalization so that VSS is always 0, we can rearrange Equation 1 as follows:

VAD = \[(-R2 × R3 × VIN ) - (R1 × R3 × VDD )\]/\[(-R1 × R2) - (R2 × R3) - (R1 × R3)\]


Each time we do this though, it's a tedious process. But Excel has a feature aptly called "Solver," which will trivialize the whole exercise once it's set up. To use Solver, you must enable it as follows:

In Windows, go to Control Panel and select the Add/Remove Software option. Select the Microsoft Office entry, and then opt for changing or updating the installation. Find Excel in the list and for the Solver Add-in, select "Run from my computer." Follow the prompts to complete the installation. Now start Excel. Click on Tools, followed by Add-Ins, and enable the Solver Add-In. Follow whatever installation prompts occur (if any).

After loading the worksheet, ADinput.xls, which can be found at www.electronicdesign.com, open it to reveal Figure 2. The input parameters are in cells B5 through B9. Cell B14 contains Equation 1 suitably modified for the minimum condition as follows:


Similarly cell B15 is modified for the maximum condition:


Cells B20 and B21 contain the formula for the input current. Notice that it sources when the maximum input voltage is applied and sinks for the minimum.

To invoke Solver, click on Tools | Solver and then make sure the entry parameters are as in Figure 3. Solver will modify cells B10 through B12, trying to keep cells B14 and B15 at zero and cells

B20 and B21 below and above predetermined project parameters. In this case, the current is limited to ±0.1 mA. You can modify this by selecting the constraint and clicking on the Change button. Note that you can work with two or more target cells by constructing all but one of the targets as constraints.

Cell B18 is used to prove that the resistor values do, in fact, generate the correct voltage range (Figure 4). By changing the value in cell B17, you can see how the value of Vad changes. Cell B17 contains Equation 4. It's possible to use Excel to generate the standard resistor values as well, but this isn't described here.

Recommended Reading:

  1. 1. Kagan, Aubrey, Excel by Example: A Microsoft Excel Cookbook for Electronics Engineers, Newnes 2004, ISBN 0750677562
  2. 2. National Semiconductor, Datasheet ADC0801, DS005671, Nov. 1999
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.