Advertisement

Blog

Excel-Optimizing Resistor Ratios, Part 1

I have often wondered whether the practice of using a ratio of two resistors to determine a particular output is a fundamental property of electronics or a concerted effort by designers. In either case, this approach is widely used, and I have already detailed several ways of finding a solution using Excel. However all of those approaches result in a solution but not necessarily the best solution.

When you are trying to get an exact value on the output of a voltage reference, what is the closest you can get? I have built an Excel model that will allow you to find the best resistor ratio. You can download the completed workbook “ResistorRatio.xlsx” here, although you may want to wait until the end of this blog series before opening it. The model uses quite a few of the techniques I have been yammering on about for several years now.

On the second worksheet of the “ResistorRatio” file you will find a complete list of all the resistor values in the 1% range. It alone may be worth the price of downloading the worksheet, and it could be used as a template for your future work. Before you think that this was a monstrous job and that I should be heaped with praise, I should mention that Excel is ubiquitous, and I simply downloaded some data from Digikey (and no doubt the data is available elsewhere in Excel format) and gently massaged it.

Figure 1

An excerpt of the resistor table on the Resistors worksheet.

An excerpt of the resistor table on the Resistors worksheet.

I wanted to try and make the model as universal as possible, but because I didn’t want to consider all 580 resistor values I chose to limit the evaluation to a resistor decade. Just follow along, and I hope things will become clearer. Figure 1 shows how I started the worksheet.

Figure 2

I named cell C3 “Target,” C4 “Scale,” and C5 “Ratio” (which you can actually see in the go-to box at the upper left of Figure 2). Cell C4 is set up with the Data Validation tool for a numeric input of -1000 to +1000. Both Scale and Ratio use the list input of Data Validation with the list in A7 to A10. I have added Data Validation Input Messages as to the purpose of the two controls as a user guide. One such input message is visible in Figure 2. I will hide column A to prevent future confusion and erroneous changes.

In order to calculate the optimum ratio I am going to create a table with possible resistor values for one of the resistors in a column and values for the other in a row. At the intersection of each column and row I am going to evaluate the ratio (or at least the expression that includes the ratio). This is a brute-force approach, but that's what computers are for! From preliminary calculations, experience, or the data sheet you will have an idea of the order of magnitude of the resistors.

I have normalized the resistor values to 1K. For the first resistor (and it doesn’t matter really which one it is, but let’s say it is the denominator of the ratio) we will enter them in a column starting at F10. If the resistor is going to be between 1K and 10K, the Scale will be set to 1. If the resistor value is between 100R and 1K the Scale will be set to 0.1. I entered the formula =Scale*Resistors!B230 in cell F10 and then copied this formula down to F123. Changing the value of cell C4 (Scale) will immediately update all the values in the column F automatically.

For the second resistor we define the order of magnitude as the ratio — if the first resistor was in the 1K to 10K range and the second resistor was in the 10K-100K range then the Ratio setting (cell C5) will be 10. If the second resistor is in the 1K to 10K range… well, I'm sure you get the idea. Now I could simply do the same entry horizontally, but I wanted to show you something different. The transpose function will take a column (or row) of cells and transpose it to a row (or column).

Being a formula means that you can modify it, and this case we will multiply it by a constant. It also has the benefit that any update in the original column (or row) we be updated in the new row (or column). The only disadvantage is that you have to know how many cells are in the destination. I figured this out quickly. Since the column goes from F10 to F123, I entered 10 in G9, 11 in G10, and then dragged the auto-increment handle unit I got to 123. That was in column DP, which I erased immediately when I knew the answer.

Please read to the end of the paragraph before trying this. I am entering am array formula, and the entry is different. Block the destination cells, from G9 to DP9. Then enter the following into the formula bar =ratio*transpose(F10:F123). Do not press enter! Press + + instead. Figure 2 shows the result. If we change the Scale, both the column and the row change. If we change the Ratio, only the row changes.

Figure 3

The values in row 9 are the transposed values in column F multiplied by the value of Ration (cell C5). The { } in the formula bar indicates an array formula and is added when the array function is entered with the 3-fingered salute <Ctrl>+<Shift>+<Enter>.

The values in row 9 are the transposed values in column F multiplied by the value of Ration (cell C5). The { } in the formula bar indicates an array formula and is added when the array function is entered with the 3-fingered salute ++.

Stay tuned. In the next part I am going to create a data table and use it to evaluate all the possible combinations. See you then.

20 comments on “Excel-Optimizing Resistor Ratios, Part 1

  1. antedeluvian
    June 10, 2014

     It alone may be worth the price of downloading the worksheet, and it could be used as a template for your future work.


    Just in case you don't get my sense of humour- the download is FREE!

  2. samicksha
    June 11, 2014

    Thank You Planet Analog, being an engineer i always wished if some one can prepare excel for me, now i have output of a voltage reference let me fulfill requirement using this excel. Hope i can modify this sheet as per my need.

  3. aerohoff
    June 18, 2014

    Great blog post! I made a simple web app that has similar functionality while I was going through the Web Programming class at udacity.com

    http://aerohoff.appspot.com/voltdiv

  4. JAYARAMAN KIRUTHI VASAN
    June 21, 2014

    AK,

    After some gap, I am back.

    Your practical and clever approach is amazing.

    Recently, I had to design an interface between my 3.3v microcontroller and a 5 V peripheral. Though my desired voltage value was not critical and can have tolerances, I too started on a similar exercise to find possible standard values that could provide me the conversion while keeping the current minimum.

    A 74LVC245 could have done the job – even I had a provision for the chip. Still, nothing can match the enthusiasm of sometimes trying things for the sake of it.

    Great post.

  5. antedeluvian
    June 21, 2014

    JK

     

    Great to hear from you again!

     

    Thanks for the compliment.

    Talking of level shifting, i did a design idea on using resistors for level shifting. Not quitebwhat you discuss, but you may find it interesting

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

     

  6. RedDerek
    June 26, 2014

    I have had one put together for some time. I have different tabs for either parallel value, divider value, or series. The user enters the desired information and then an error spread allowed, then all possibilities are presented. The tighter the error, the fewer choices are presented. There is also a section that addresses the resistor tolerance factors. The spreadsheet is geared for 1% values and equations are used to calculate the values.

    I would be glad to share. Here is the DropBox link

    https://drive.google.com/file/d/0BzMAGeZwSD5aVU9KZ3hwQlV0dGs/edit?usp=sharing

    Feel free to email me any questions or suggested improvements at:

    It is not pretty, but with some fiddling, you should get the idea as to how I work with this.

    Oh, the ratio tab also has the ability to account for bias current into an opamp, etc which could affect the actual resistor divider answer.

  7. Sachin
    June 30, 2014

    @Aubrey, thanks for the post. I never knew such automated resistor ratio calculator is available freely on Digikey website. Thanks for uploading it here. Now its very easy to find required resistor values.

     

  8. Sachin
    June 30, 2014

    I made a simple web app that has similar functionality while I was going through the Web Programming class at udacity.com

    @aerohoff, thanks for sharing your web app. I just checked the link. I am curious to know what is the need to take voltage input and voltage output values from the user because I didn't find the output resistor values are dependent on voltage.

  9. Sachin
    June 30, 2014

    A 74LVC245 could have done the job – even I had a provision for the chip.

    @AK, 74LVC245 is good choice but only issue is it has 8 channels for shifting, but these are not suitable for I2C. 

     

  10. Sachin
    June 30, 2014

    @RedDerek, thanks for sharing that excel with us. I am curious to know if we would like to change the % error where should we change it ? I tried changing B8 value in Ratio tab, do i need to change the value in some other place as well?

     

  11. aerohoff
    June 30, 2014

    SachinEE, the app allows the user to enter either the ratio, or the input and output voltages, which are used to calculate the ratio. No need to enter all three. Perhaps that is not clear. Frequently you know you have 12v and want 2.5v out, but you might not have bothered to divide the two.

    An improvement I should make would be to consider power disipation. My app would currently recommend the same resistors for a 12V to 2.5V divider as it would for a 1200V to 250V divider (115Ω and 437Ω). The latter divider would disipate 2600W (not ideal).

    Also, 437Ω is not stocked on digikey, so I wouldn't want to use it in a design. I'll need to address that too.

  12. RedDerek
    June 30, 2014

    @SachinEE – changing the value in B8 will change the number of visible results in the “Usable set” table area. Essentially, the results listed in the Usable Set table is only shown if the error is better than what is entered in B8.

    I look at the Usable Set table to see what set best meets my design needs. For example, if you want a set that provides a certain total series resistance, you can add the two numbers and then use that set for the circuit. In the case of a power supply you may want the top and bottom resistors to add up to 100k equivalent.

    One key note: I do think about the factor of 10 when looking at the result table. So if you want values in k-Ohms, then keep that in mind when looking at values.

    Each tab operates independently of the others.

  13. amrutah
    June 30, 2014

    @SachinEE:  “what is the need to take voltage input and voltage output values from the user because I didn't find the output resistor values are dependent on voltage.”

       In a voltage divider, Say Vhead is the input voltage and vtap is some output voltage which is from the resistor divider then,

        Vtap= Vhead*(Rb/(Rt+Rb)).  To calculate the resistors, we need atleast 2 of the voltages and then assuming the total resistance RT=Rt+Rb, can calculate the Rb.

      I did not understand your point..

  14. amrutah
    June 30, 2014

    @SachinEE: I am sorry, I saw the web app after posting the previous question.  I understand your point and the weakness in the webapp.

  15. amrutah
    June 30, 2014

    @Aerohoff: You need to consider either the ratio or the absolute voltages (you can disable one of the user inputs).  Best you can do is to ask the user to input, Vin, Vout, Total current from Vin (To decide the total resistance) and come up with the ratio.  You can then add complexity like Watt, Multiple Vout taps, resistor network reduction and so on.

  16. Sachin
    June 30, 2014

    I do think about the factor of 10 when looking at the result table. So if you want values in k-Ohms, then keep that in mind when looking at values.

    @RedDerek, thanks for the keynote. I will keep it in mind when using your work sheet.

  17. Sachin
    June 30, 2014

    I did not understand your point..

    @amrutah, my point was with respect to the web app link that he has uploaded. If you visit that link then you will see that the voltage values doesn't have any impact on the outcome. 

  18. vasanjk
    June 30, 2014

    AK

    That's a nice article.

    By the way, I made a level shifter with smd resistors and transistors as I needed multiple such shifters in a design. The circuit was a PCB with a minimum of two back to back converters. I could stack as many as I wanted.

     

    JK

    P.S- This is my screen name henceforth.

  19. vasanjk
    July 15, 2014

    sachin

     

    It is very much suitable. It is not a shift register. It is an octal buffer. I regularly use it with my 3.3v mcu for interfacing with other 5v peripherals.

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

Leave a Reply

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