I started writing a series of blogs on the use of Excel spreadsheets for circuit design on the now-defunct Microcontroller Central. Those blogs, though separate from this blog and future ones that I will write on Excel, will create a more complete picture with their presence, so Planet Analog has added them to its database. (They can be found in the Related Posts list at the bottom of this blog.)
In this blog on Excel, I would like to discuss how the Data Table feature can help with the design process. Let's consider a 555 timer configured as an astable multivibrator, as shown in Figure 1.
The mark-to-space ratio is given by RB/(RA+RB).
The Data Table function can handle one or two input variables and can be presented vertically or horizontally in the worksheet. I will discuss only the vertical presentation in this post. The translation to horizontal format is straightforward, and I will leave it to the user as an exercise (as my professors used to say).
Let's pose a scenario: We want to configure the 555 to work at up to 100 kHz with a potentiometer installed as RA. We will choose RB as 4.75kΩ and C as 0.1μF. Set up the worksheet as in Figure 2. The values in column C are possible pot values, and the result of the frequency calculation appears in cell D4. The relative positions are important. The column containing the values to be considered (column C) must start at and be to the right of the cell whose value it is going to replace (cell B5). The formula to be evaluated should be one row up and one column to the right (cell D4).
Now block (using click and drag) the area containing the variable, the calculation, and where the results are to be placed. In other words, block C4 to D11. Then click on the Data tab on the Excel ribbon followed by What-If Analysis in the Data Tools subdivision. Then select Data Tables. In the resulting dialogue box, enter the source variable that will be analyzed. You should be looking at something like Figure 3.
Click on OK, and you should see the result in Figure 4.
is paired with possible pot values (maximum).
From the results in cells D5 to D11, we can see we get nowhere near the desired 100 kHz. If you modify the values in cell B6 (RB) and cell B7 (C), you can see the result change dynamically — something that was not possible in the Goal Seek and Scenario approaches we have seen in previous blogs. If you click on the result cells, you will see that Excel has put rather strange formulas here. These are known as array formulas. (This tidbit is just for future reference and will be on the test.) Changing the entry for C to 1nF (0.000000001) will bring the frequency range into focus. I found this by trial and error.
It is possible to add additional calculations. For instance, let's say we wanted to see how the mark-space ratio performed with these values. Let's add a new column for the mark space calculation. This is shown in Figure 5.
We then repeat the data table process with a minor modification of the data area, as you can see in Figure 6.
though this is not an issue here.
The Data Table analysis can be stretched to a second dimension. Let's see how the frequency will change as we manipulate both RA and RB. Let's rearrange the data so that the frequency calculation is in cell C23, with the first variable (RA) in a column beneath it and the second variable (RB) stretching out to the right of it, as you can see in Figure 7.
Block the formula, input variables, and output area, as shown in Figure 8. Then, as before, click on the Data tab on the Excel ribbon, followed by What-If Analysis in the Data Tools subdivision, and select Data Tables. Select the row and column input cell — the cell referenced in the formula that will be evaluated according to the input data into the row or column.
Click on OK to complete. For completeness, I have shown the result in Figure 9.
Since you can change any of the input variables and see the immediate result, you could build this into a generic model and then twiddle the values to get the desired results. It's not particularly elegant, but maybe it will help in some instances. Knowledge empowers. You could also enhance the performance by adding features like conditionally formatting the cells in the result to indicate when a condition (like the desired frequency) is met. There may be more on conditional formatting in a future blog.
The ideas I have presented are a little contrived for two reasons. First, pulling a good example out of the air is not always easy. Second, I wanted to use this to show how much more powerful the Solver tool is. That tool will be discussed in my next blog on Excel in a few weeks.