Circuit simulation and software workbooks like Matlab and Jupyter are great for being able to build things without a lot of overhead. But these all have some learning curve and often use clever tricks, abstractions, or library calls to obscure what’s really happening. Sometimes it is clearer to build math models in a spreadsheet.

You might think that spreadsheets aren’t built for doing frequency calculation and visualization but you’re wrong. That’s exactly what they’re made for — performing simple but repetative math and helping make sense of the results.

In this installment of the DSP Spreadsheet series, I’m going to talk about two simple yet fundamental things you’ll need to create mathematical models of signals: generating signals and mixing them. Since it is ubiquitous, I’ll use Google Sheets. Most of these examples will work on any spreadsheet, but at least everyone can share a Google Sheets document. Along the way, we’ll see a neat spreadsheet trick I should probably use more often.

## Start at the Ending

This is what the final spreadsheet looks like. There are two sine waves and we mix them together to get a sine wave that would decompose into the sum and their difference of the inputs. These are all real numbers — we’ll get to complex numbers in a later part of the series.

You’d think that mixing two signals would be adding them together. Turns out, adding in the frequency domain looks like multiplication in the time domain, so we actually want to multiply the two signals together. In general, a good math model for a sine wave is: `y=A*sin(ωt+Φ)`

Let’s dissect what this equation to see what it’s all about.

The sine angle is in radians and ω is the frequency in radians. The Φ stands for a phase shift. So you can think of A as the amplitude — if A is, say, 5 then the output will go from -5 to 5. The frequency tells us how many “full circles” the signal will make in a second. Another way to say that is radians per second, which I’ll explain in depth in just a moment. The phase shift is how far the wave shifts (in radians) from a wave with zero degrees of phase shift. The t parameter, of course, is time in seconds.

## What the Heck Are Radians?

If your high school math is rusty, 360 degrees is `2π`

radians. In math terms, a radian is related to an arc connecting the ends of an angle. To find radians, measure the length of the arc and divide by the radius of the circle. If the arc is the whole circle, you wind up with the arc’s length being the circumference of the circle — `2πr`

— and `2πr/r`

is just `2π`

. In practice, you convert degrees to radians by multiplying by `π/180`

. For frequency, you multiply the cycles per second (Hertz) by 2π.

If you think about a sine wave, it is really showing a point on a moving and rotating circle. So each cycle is 2π radians. By multiplying the frequency in Hertz by 2π, we are converting to radians per second.

Look at the 90 degree angle in this diagram. The radius, R, is the same no matter where it touches the green arc. The arc, on the other hand, is `2πR/4`

(since it is 90 degrees, or 1/4 of a full circle’s circumference). Then when you compute the radians the R cancels out leaving π/2 radians. Radians define how much of an arc the angle describes on any circle, no matter the actual size of that circle. If you work the math out, π/4 is 45 degrees, π is 180 degrees, and 2π is a full circle.

The phase is similar, too. A phase shift of 180 degrees (π radians) will cause the sine wave to be inverted relative to a sine wave with zero shift. A π/2 shift will line up the peak of the shifted sine wave with the zero crossing of the original sine wave. It is worth noting that a -90 degree phase shift of a sine wave is also called a cosine wave. As you can see below, the cosine wave starts at 1 and drops while the sine wave starts at 0 and increases.

## Spreadsheet Tricks

All this math is pretty simple to set up in a spreadsheet. There are at least two ways to go. You can do lots of formulas, or you can use a trick to do one formula for each signal. Either way, you are going to need a timebase.

Here’s my signal generation spreadsheet. The timebase is in column B and uses the sample rate in A16. Cell B2 contains a zero. The next cell contains the formula `B2+1/$A$16`

which finds the time for the rate in A16 and adds it to B2. The dollar signs mean that when I copy and paste that to another cell, the spreadsheet won’t change the A16 reference. In fact, copy this to cell B3 and you get `B3+1/$A$16`

.

You could also write a formula like `(ROW()-1)*1/$A$16`

That would do the same thing and there are many other ways to get the same effect. Either way, it is easy to copy the fromula from one cell then select from B3 to B1000 and paste. Your time base is done.

You can do the copy and paste with the signal columns and create a formula to paste. For example, cell C2 could be:

$A$8*sin(2*pi()*$A$4*$B2+radians($A$12)

Then you can copy it all the way down. However, using `ArrayFormula`

is a much more clever way of doing this. The ArrayFormula function interprets ranges and uses them to form an array under the formula. That is easier to see in an example. Suppose you have the timebase in column B and for some reason, you want to multiply B1 by B2, B2 by B3, and so on. You could write: `ArrayFormula(B1:B10*B2*B11)`

. You can use scalars, too, like `ArrayFormula($A$4*B1:B10)`

.

This allows you to write (and more importantly, edit) one formula that fills in an entire array of data. Another nice feature, if you don’t like dealing with cell references, is the named range. If you go to Named Ranges on the Data menu you can do things like name A4 as Frequency1 and then use that instead of $A$4 in your other cells.

## Graphs

The real fun begins with getting graphical outputs. Spreadsheets are typically good at doing this and you can see from the screenshots that plotting the two sine waves against each other works well. Mixing the two together in column E and graphing that is illustrative, too.

The key is to not try to graph all of the data at one time. The charts in my spreadsheet are doing 200 points (400 ms). Use the Line Chart type and then you can modify things like ranges and headings as you like. Looking at column E probably doesn’t tell you much, but the graph shows how the signals mix together quite nicely.

There are about 10 cycles of the high frequency and at 400mS full scale that works out to 25 Hz, the sum of the 10 and 15 Hz inputs. The larger slower component has two full cycles and that’s 5 Hz, the difference of the inputs which is just what you’d expect.

You can play with the various parameters to see the effects it has on both graphs. Armed with this kind of math model, you can start tackling other signal processing ideas: filters, IQ modulation and demodulation, and even more. But those are topics for another post.

“Turns out, adding in the frequency domain looks like multiplication in the time domain, so we actually want to multiply the two signals together.”

I’m not understanding this. Since you’re simulating the analog addition of two signals at each time tick, it seems they should simply be added together to get the sum. This is what would happen if the signals were added in a real world analog mixer for instance.

Multiplication would only seem correct if you were simulating a ring-modulator or some other non-linear system.

Can you explain further please?

In the frequency domain, the signal is represented by complex numbers. If you see complex numbers as vectors in the complex plane, multiplying complex numbers is the same as rotation about the origin. The rotation rate is the frequency. The length of the vector is the magnitude. Multiply a vector by its complex conjugate (swap sign of the imaginary part) to get its magnitude.

In some cases you can multiply by real numbers because the results will be the same, such as when the imaginary parts are all zero.

At first I was confused too, until it hit me that “frequency mixing” (as is used for example in radio receivers) was the goal, not “audio mixing”. With audio mixing/summing the signals would indeed be simply added together.

Ahhh, so it is like modulation then.

Thanks for the clarity

This is AM modulation. It’s good, but not great.

In the past, I have used Excel for this type of stuff. If you call o e signal “carrier” and make it big, you can do a FFT of the mixed signal. Graphing that will show the carrier and sidebands.

When you want to set up SSB sideband, it’s easier to just add 3 distinct signals. I typically used 9.995MHz, 9.998MHz, and 10.004MHz. Getting the 4K USB away from the 3K and 5K LSB is quite an exercise.

I never did get FM to decode correctly. The ATAN function in Excel always tripped me up. I’d like to see that covered here.

Maybe you can simulate a phase locked loop?

” In fact, copy this to cell B3 and you get”

I’m waiting for the rest :-)

I’ve got to say, as a DSP newbie these articles from Al have been absolutely incredible! Always looking forward to more of them

+1

I’ll testify to spread sheets for analyzing signal generation algorithms. I used spread sheets to identify and correct and glitches I was experiencing when sweeping frequencies while I was developing my, still incomplete, C64 Emulator (https://sourceforge.net/projects/sasm65xx/). Latter it helped when I was trying to find a simple way to do high pass and low pass filters.

/*

* Calculate wave

*

* Inputs:

* time [s]; frequency [Hz]; amplitude [decimal]; phaseShift [degree];

*/

function wave(time, frequency, amplitude, phaseShift) {

function freq(hertz) {

return hertz * Math.PI * 2;

}

function radian(degree) {

return degree * (Math.PI / 180);

}

return amplitude * Math.sin(freq(frequency) * time + radian(phaseShift));

}

Same thought here :)

“Turns out, adding in the frequency domain looks like multiplication in the time domain, so we actually want to multiply the two signals together.”

TLDR:

This statement is wrong.

An addition is always an addition indepentent whether it’s made in time domain or in frequency domain, it stays an addition.

A multiplication in one domain is a convolution in the other.

Details:

To get from time domain to frequency domain you need the Fourier Transform, which is an integral. For an integral it doesn’t matter if you sum to variables together before doing the integral or to do the integral on those variables first and then sum them. It’s the same. Therefore an addition in one domain is still an addition in the other domain.

When you mix (mathematical: mulitplication) two (real valued) signals (sine waves) you get two signals (sine waves) – one at f1+f2 and one at f1-f2. This is a direct result of the convolution.

A real valued sine wave in time domain results in two (dirac) peaks in frequency domain – one at f1 and one at -f1. You can thing of two rotating arrows that rotate in sync in opposite directions, which cause the imaginary parts to cancel out resulting in only real values. This holds true for two real valued sine waves (with peaks at: (-f1, +f1), (-f2, +f2) in frequency domain).

The convolution creates peaks at the combinations of these two (peaks at: -f1-f2, -f1+f2, +f1-f2, +f1+f2).

You can see the difference when you you try it.

I think it is a simplification of the fact that convolution in the time domain is multiplication in the frequency domain, with special cases for real data, etc. Not sure. One has to be careful with the math. Adding two signs can look ALMOST exactly like multiplying two other frequencies, but there is a phase shift in one not found in the other. In the addition case for two signals close together you get the beat frequency from the change in amplitude as the two signals interfere. It has a 180 deg shift per period. If you multiply one of those signals by a signal at that beat frequency you get an output that looks the same except there is no phase shift. To the eye they look the same but their FT’s are quite different. http://fooplot.com/#W3sidHlwZSI6MCwiZXEiOiJzaW4oeCoyMHBpKStzaW4oeCoyMXBpKSIsImNvbG9yIjoiIzAwMDAwMCJ9LHsidHlwZSI6MTAwMCwid2luZG93IjpbIjEiLCI1IiwiLTIiLCIyIl19XQ– http://fooplot.com/#W3sidHlwZSI6MCwiZXEiOiJzaW4oeCoyMHBpKSpzaW4oeCpwaS8yKSIsImNvbG9yIjoiIzAwMDAwMCJ9LHsidHlwZSI6MTAwMCwid2luZG93IjpbIjAiLCI0IiwiLTEiLCIxIl19XQ–

Isn’t multiplication B1 with B2, B2 with B3, … etc. rather “ArrayFormula(B1:B10*B2:B11)” than “ArrayFormula(B1:B10*B2*B11)” ?

I used spreadsheets, literally decades ago, to demonstrate what harmonics do, in particular the odd, additive harmonics including the infamous 9th, odd triplin, with an emphasis on their effects on the telephone network (back in the good old landline days.) I was a Transmission Technical Support Engineer for NY Telephone Company, with responsibility for noise mitigation and I had a MIT educated electrical engineer working for Niagara Mohawk power company, trying to convince me that NIMO could not be causing the noise issue, even though I had just demonstrated that they could, would, and in this case, did, cause my telephone system to noise up. I pulled out my spreadsheets that showed what the effects of the harmonics his ringing transformers and misplaced capacitors and unbalanced transmission lines were generating and he accused me of making up this stuff and virtual witchcraft.

The term ‘Mixing’ is just confusing and is still a bad choice when you want to describe the operation performed by modulating a carriers amplitude. Especially when you leave out the fact that the ‘sum’ of the frequencies as you call them are the actual mixing products of the ‘Mixer’. Besides you just messed up time-frequency fourier relations by stating that adding in freq is multiplication in time. It is just plain wrong as stated before and even more confusing for newbies who meant to learn from your article. To confuse you even more, there is multiplicative and additive mixing both describing an amplitude modulation.

Anyways, what does all of this has to do with Pacman?

This article seem very pious…https://tauday.com/