In part 1 of this blog, we managed to establish the framework for a data table to allow us to evaluate each possible combination of the whole range of resistors. The next step is to populate the data table.
Since it's my choice, I will start with an LM317. The output voltage of this adjustable voltage regulator is given by Vo = (Vref*(1+Rnum/Rdenom))+(Iadj*Rnum). Which resistor is which is covered in the data sheet, and I don't think I need to replicate the drawing here. Vref is nominally 1.25 V, and Iadj is nominally 50 µA. I am not going to build the tolerance of these into this model. From the data sheet, Rdenom is suggested to be 270R, and Rnum is likely to be a factor of 10 greater.
On the worksheet, I set the scale cell to 0.1 (for the 270R) and the ratio to 10. I also added the parameters in cells C10 to C13, as you can see in Figure 1.
Cell C10 is named R_NUM, and cell C11 is named R_DENOM. I didn't name cells C12 and C13, since the name would remain when a different equation was entered for a different device.
I entered the formula to be evaluated in the required spot at the origin of the table (cell F9):
=Target-($C$12*(1+(R_NUM/R_DENOM)))+(C13*R_NUM). This is in fact the error of the resulting voltage compared to the desired output. Block from F9 to DP123 (actually, I found it easier to go from DP123 to F9), and then click on the Data Table option (under the Data ribbon tab and then What If Analysis).
Enter the data as in Figure 2, and -- abracadabra -- the whole data table is populated in a flash. Now, if you had a month of Sundays, you could scan the data looking for the minimum, but we can improve on that. To do this, we will use the MIN function, but the result we want would be confused by positive and negative values in the table, so first we must modify the formula in cell F9 for absolute values to
Defining the data table.
Next enter the formula =MIN(G10:G123) in cell G124, and copy it from H124 to DP124, so the minimum for each column appears below that column. Though we will arrive at a single resistor pair in the end, we will initially look up the row for each minimum. To do this, enter =MATCH(G124,G10:G123,0) in cell G125, and copy that from H125 to DP125. Note that the number is the row of the table, and not the row number alongside the worksheet.
Once we have all the minima for each column, we want to find the minimum of these minima. For this, we enter the formula =MIN(G124:DP124) in cell F124. For this setting in this example, F124 has the value 0.000813. Correspondingly, we need the column for where this minimum occurs. In cell F125, enter the formula =MATCH(F124,G124:DP124,0). It finds the 0.000813 value in column 39. Again, note that the number is the column of the table, 39 columns from column F.
To extract the resistor value for Rdenom, we have to do an intermediate step and go back to column 39, which we decoded, and fetch the row location stored in row 125. In cell F126, we use the formula =INDEX(G125:DP125,,F125), which returns the value of 91. As a confirmation, if you place the cursor in cell F124 and hit the right arrow 39 times, you will arrive at cell AS124, which contains the value 0.000813. Beneath it is the row in which this minimum occurs, 91 (in cell AS125). This is where the values in cells F124 to F126 come from (see Figure 4).
Now we will use this information to extract the resistor values. In cell F127, enter the formula =INDEX(G9:DP9,,F125), which looks for the 39th (in cell F125) entry in the horizontal list of resistors, returning a value of 2,260 ohms. Similarly, we look at the vertical list of resistors by entering the formula =INDEX(F10:F123,F126,) in cell F128. We can see that Rdenom of 2,260 ohms and R of Rnum ohms will give an error of 0.000813 volts.
Enter the formula =VLOOKUP(F127,ResistorTable,2,0) in cell G127. This will return the engineering notation for the value. Copy the formula to G128, and both cells report as desired, fetching the values from the second column of the resistor table on the second worksheet.
We can use conditional formatting to highlight the intersection where the minimum occurs. First, block the whole data table from F9 to DP123. Then select Conditional Formatting, and set it up as you see in Figure 3.
Setup of first condition for the rows, where the row number equals the number
in cell F126 (with offset), to be highlighted with green.
Add a second condition with the formula equal to =COLUMN()=(6+$F$125)), so that the column whose number matches the value in F125 (with offset) will also be highlighted in green.
The completed worksheet. Note that I created four panes to shrink the table
to show the intersection of the highlighted row/column.
So there you have the completed worksheet. You do not have to recreate it when you want to use another device. Simply rework the formula in cell F9 to suit your needs using the R_DENOM and R_NUM terminology, and you will get the results instantaneously. You can find the file ResistorRatio.xlsx here.
I have now completed the series on Excel that I set out to write nearly two years ago. There are many features that I have not approached. It was my intention to avoid macros, because of the limited format of a blog, and I still have to figure out how to use pivot tables and apply them to electronics, to mention but two. So there is a possibility of some blogs in the future, but I am sure I hear some sighs of relief out there, now that I am done.