Simulating a transfer function

Some may be wondering, how did I simulate a transistor (or tube) transfer function in Excel? Well, I owe it all to my time at Maxim and Tektronix. In both of those places, fluency in Excel was required to do the job, and somewhere along the line I learned to use the VLOOKUP function. Some people consider VLOOKUP difficult to understand, but there is so much help on the web for this function, you really don't have an excuse.

The idea is to create a two column table. In the first column I created a linear ramp to describe a perfectly linear transfer function from -1.0 to +1.0, and in the second column linked to the first I created a list that contains the transfer function that you are going to sully with imperfections to simulate non-linearities. When I first tried these experiments I used 2048 samples in my VLOOKUP table, but it gave me some unbelievable results, so I decided to go as big as I dare and settled on 10,001 point table. This seemed to give me very believable results, and as I did more and more experiments, I came to believe this was a good value. Especially because, as it was, it was taking up to an hour to run a test with the FFT running on this gigantic spreadsheet. So if you are going to try this, be sure to use a very large VLOOKUP table. Here is a peek at my table, with most of the data missing of course, since I don't think you want to look at 10,001 data points.

At the top of this article you can see a graph of the perfectly linear transfer function. However, let's say you wanted to clip the top off a waveform to see what harmonics result. Instead of letting the value in Column 1 increment along with Column 2, I freeze it at some point, in this case, 400 points from the end at a value of 0.919991999. 400 points out of 10,001 is a 4% distortion, and that results in 1.6uV-s of area being removed from my transfer function in the form of a small triangle. That assumes that my transfer function is 1 millisecond wide, the width of a single cycle of a 1KHz sine wave, which is what we traditionally use to test THD in an analog circuit. This is what I call Total Distortion Energy or TDE. By measuring the area of the distortion, we are quantifying the distortion in terms of energy, which is what AC signals are all about, and it works (if you read the book).

Then I create a sine wave of 2048 samples (since the Excel FFT can't go any higher than 2048 time domain samples). I like having my frequency bins spread out a bit, so I chose an M of 19 in my sine wave formula. I could have used any odd number, even a non-prime, however, I don't want to add confusion and complexity while I'm trying to teach something. I'm trying to get my point across, not show how smart I am. However, in order to see a single cycle of the sine wave, I have to re-order the wave to a single cycle wave, which is why all my Time Domain plots have the label Reordered TD Wave on them. Watch for it.

Anyway, I finally have a column in my Excel spreadsheet that is filled with 2048 entries that look like this:

=VLOOKUP(E4,$A$3:$B$10003,2, TRUE)

This statement says take a look at the value in the sine wave column (E4 in this case), then go through the VLOOKUP table from A3 through A10003 and when you find the value closest to what you have in the sine wave value, substitute the adjacent value from the B column. The TRUE says that I will take an approximate match, which is the only way to go when you are dealing with experiments like this. So when I run the simulation, the sine wave goes through the VLOOKUP table point by point (10,001 points long) until it finds a close match, then it goes to the next value in the sine wave column (column E) and does the same thing. Eventually we end up with a facsimile of our sine wave after it has been applied to our simulated transfer function. In this case it looks like this:

Notice that the little tiny 1.6uV-s distortion, while only 4% of the entire transfer function really destroyed this otherwise beautiful sine wave. That is how we simulate peak clipping. If I wanted to do it on the other side, I would muck up the B column at the beginning of the 10,001 point table and that would cause the negative side to get distorted. However, if I want to muck up the middle of the wave, for example to simulate crossover distortion, I would do something like this:

Here I modified 400 points (4%) of the B column to a value of zero just above where it hits zero. That makes it hold the value of zero for 400 samples and creates a 1.6uV-s triangle. Now, what will the E column do when it runs through the VLOOKUP table?

Look closely! You can see the impact at the very beginning of the sine wave, and in the dead center of it. Notice that it's much smaller than that nasty clipping distortion that we saw earlier, even though the area removed from the transfer function is exactly the same as our peak clipping distortion (1.6uV-s). And that my friends is the problem with THD, the clipping example has a THD of -32dB, but the zero crossing example has a THD of -45.5dB, a whopping difference of 13.5dB for the exact same distortion anomaly just because it's in two different places, one at the peak and one at the zero crossing. That means that a Class A amplifier is going to have a worse THD value than a Class B amplifier even if they have exactly the same amount of distortion. That makes THD a lie!

Unfortunately, the example Excel simulator spreadsheet that I provide to people to try this stuff out does not use the VLOOKUP table because it's just too cumbersome to teach people how to use it. But now that you know the secret of how I discovered the true cause of harmonics, you can take my file and make your own VLOOKUP table and duplicate my experiments. And then you can begin the painful process of kicking yourself in the ass for not being the first person to figure this stuff out. You're welcome.