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