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 V_{o} = (V_{ref} *(1+R_{num} /R_{denom} ))+(I_{adj} *R_{num} ). Which resistor is which is covered in the data sheet, and I don't think I need to replicate the drawing here. V_{ref} is nominally 1.25 V, and I_{adj} is nominally 50 ÂµA. I am not going to build the tolerance of these into this model. From the data sheet, R_{denom} is suggested to be 270R, and R_{num} 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**

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**

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 R_{denom} , 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 R_{denom} of 2,260 ohms and R of R_{num} 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**

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**

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.

AK,

Is it possible to use excel to design inductors?

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

Â

Hi JK

Is it possible to use excel to design inductors?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.

step 1. Learn about inductors.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.

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

Canada 12

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

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.

@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.

@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.

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.

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.

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.

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.

Â

@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.

You may visit the following to see if it helps:

fairbird.ucoz.com

MalcomY

You may visit the following to see if it helps:fairbird.ucoz.comThis looks very useful- thanks for the pointer

Â

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

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