 # Excel-Optimizing Resistor Ratios, Part 2

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.

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 =abs(Target-(\$C\$12*(1+(R_NUM/R_DENOM)))+(C13*R_NUM)) .

Figure 2 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.

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.

Figure 4 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.

## 15 comments on “Excel-Optimizing Resistor Ratios, Part 2”

1. JAYARAMAN KIRUTHI VASAN
June 21, 2014

AK,

I know it is a daunting task, still, would like you to consider writing some blogs on this subject.

2. antedeluvian
June 21, 2014

Hi JK

I am sure it can be used. Anything that can be expressed as a mathematical formual can be calculated- building a logical model- that is something else. I believe Ridley Engineering have an inductor (for SMPS) design package that is rooted in Excel (but I could be wrong).

I know it is a daunting task, still, would like you to consider writing some blogs on this subject.

Don't hold your breath! Actually I am planning a blog that will detail the design of a custom transformer as part of a AC Current Generator (reprising the design that appeared innCircuit Cellar Online many years ago). but there is no excel.

3. antedeluvian
June 23, 2014

I often wonder how many read my blogs and whether the effort is worth it. Many times I put quite a few hours into it and there are precious few comments as you can see in this case. To help me decide whether I should continue I put a counter on the download in this blog.

Here are the results:

There were a total of 141 downloads (there may be duplicates of course) from these places:

United States 68

United Kingdom 10

Germany 6

Australia 5

France 4

Italy 4

Hong Kong 3

Brazil 3

New Zealand 3

Taiwan 2

India 2

Spain 2

Israel 2

Sweden 2

Belgium 1

Denmark 1

Finland 1

Russian Federation 1

Netherlands 1

Portugal 1

Norway 1

South Africa 1

Argentina 1

European Union 1

Korea, Republic of 1

Singapore 1

Austria 1

4. geek
June 25, 2014

Interesting post as always, Aubrey. Reading your blog has helped me refresh my Excel skills. I remember using the combination of data tables to generate a series of values and then using conditional formatting for a regression model that I was working on for an academic project. I guess Excel has a lot to offer to engineers but it's mostly the business professionals who tend to have more expertise in Excel than engineers.

5. geek
June 25, 2014

@Aubrey: I think your next series of blogs can be based on MATLAB. In my academic and professional experience, I have mostly used MATLAB and that seems to be the norm for most engineers. I think that may be of great help to the current students and professionals as well as those aspiring to be one. However, I do not wish to undermine the importance of Excel. It's as powerful a tool as any other.

6. RedDerek
June 26, 2014

@Aubrey – check my comment in the part 1 of this set for I provided a link to my resistor calculator page that I have been using for some time. It might provide some other alternatives to your method.

I actually use formulas to calculate variations and work with the closest 1% set of resistors possible.

7. Sachin
June 30, 2014

I have now completed the series on Excel that I set out to write nearly two years ago.

@Aubrey, thanks a lot for the series on excel. We learnt a lot about excel and its application in calculation circuit variables. If possible can you post some blogs on how best we can use to calculate statistical parameters in excel.

8. Sachin
June 30, 2014

I do not wish to undermine the importance of Excel. It's as powerful a tool as any other.

@tzubair, I totally agree with you. Excel is very powerful tool and people have used excel to build very complex models. But only drawback of excel is if the data-points becomes very huge then its becomes pretty slow.

9. Sachin
June 30, 2014

I think your next series of blogs can be based on MATLAB.

@tzubair, I totally agree with you. Tutorial on MATLAB will help both students and professionals. I really hope to see Matlab tutorials in future blogs.

10. Sachin
June 30, 2014

I am sure it can be used. Anything that can be expressed as a mathematical formual can be calculated- building a logical model- that is something else.

@antedeluvian, I totally agree with you. But if the mathematical formula is too complex then its better to implement it using macros because writing bigger formula is not easy.

11. amrutah
June 30, 2014

@SachinEE: “But if the mathematical formula is too complex then its better to implement it using macros…”

I agree with you that the regularly needed equations can be automated by writing macros, if its one time use then no purpose is served.  But writing macros itself  needs a lot of testing.  There are a set of regularly used macros bundled and available in web, check for “ASAP Utilities” on Google.

12. MalcolmY
April 23, 2015

You may visit the following to see if it helps:

fairbird.ucoz.com

13. antedeluvian
April 23, 2015

MalcomY

You may visit the following to see if it helps:

fairbird.ucoz.com

This looks very useful- thanks for the pointer

14. antedeluvian
March 15, 2017

Unfortunately Dropbox has chnaged their implementation of public files. This has affected many, many instances where I have linked to my files. I am trying to update the links as I come across them. Dropbox will ask you to register in order to copy the file or open the file (you can only open the file once you log in)

In this blog this is the revised link to the project ResistorRatios

15. hanrysmith
June 26, 2020

really amazing and great article i loved it and the information that you have mentioned is really helpful and valuable so thanks for your amazing effort and keep sharing
click on the link below now
https://www.roobotech.com.au/t/refurbished-hands­ets

This site uses Akismet to reduce spam. Learn how your comment data is processed.