Build up a square wave from its Fourier components (Calc or Excel)
According to Fourier's Theorem, a periodic signal can be split up into the sum of sinusoidal signals with frequencies multiple of the original signal's frequency. These sine waves are called "harmonic components". In this tutorial, we're going to approximate a square wave signal by means of its first harmonic components and plot each of those components as well as the signal obtained by superimposing them.
We're going to use what we have learned in a previous tutorial about the representation of a sine wave. We're not going to explain in full detail how to build up each one of the signals we will need; in case you need a more detailed explanation, please refer to that tutorial.
Create a table containing the characteristic parameters of the harmonic components
In the upper part of the worksheet, set a table like the one in the following figure:
We'll use this table to set and edit the characteristic parameters of the sinusoidal components of the signal that we want to approximate.
Since we're going to represent a square wave with a frequency of 5 Hz, we'll write multiples of this value in the frequency row. Rather than writing the numerical values (5, 10, 15, 20, 25) "by hand", we choose to use a formula, so that, once the fundamental frequency is set, its multiples are computed by the program.
After writing 5 as the fundamental frequency value in cell B3, put in C3 the formula that you see in the following image:
Now, grab the lower-right corner of cell C3 and drag it to the right:
Once you get to cell F3 and release the mouse key, you will see in cells B3:F3 appear the multiples of the fundamental frequency:
As expected, while copying the formula, the reference to the fundamental frequency (cell B3) has remained fixed, while the reference to the number of the harmonic has changed (because we have used a relative reference). As an example, look at the content of cell D3 by selecting the cell and reading the formula that appears in the formula bar:
As for amplitude and phase, just copy the values that you see in the figure for now. You'll understand later where those values come from and you'll insert the mathematical formula to compute them automatically:
Create a column for the ascissa axis
Just like in the previous tutorial, before creating a column for the ascissa values (instants of time in our case), let's preserve a space where to set the sample interval (i.e. the time interval intercurring between each sample and the following one).
We can do that at line 6, leaving a one-row separation from the table that we have built in the previous point:
Instead of writing "Sampling interval", we have used the symbol that usually represents this quantity, i.e. TS . In order to write the subscript, open "Format" menu, click on "Character...", go to the "Font Position" tab and select "Subscript".
As in the two previous tutorials, we are going to list the values for the horizontal axis (which we call t axis, since we want to represent the signals' variations with respect to time) in the first column of the worksheet.
Leave once more an empty row and enter a t (italic and centered) in cell A8.
Now you should be able to fill column t starting from 0 up to value 1, with increments of 0.001 between each value and the following one (remember to use a formula and, instead of 0.001, an absolute reference to cell B6, the one that contains the value).
Compute the y-values of the sinusoidal components
In the columns from B to F, let's insert the formulas to compute, point by point, the harmonic components of the square wave. In other words, in each of these columns we must set a sinusoidal signal, just like we did in the previous tutorial.
For each harmonic component, the formula will reference the three cells laying in the upper part of the same column, containing the values for the amplitude, frequency and phase of the sine wave.
Look carefully at the formula we've just inserted: unlike what was done in the previous tutorial on the contruction of a sinusoidal signal, this time we haven't used absolute references for the amplitude, frequency and phase; instead, we have set some sort of "hybrid" reference, "a bit relative" and "a bit absolute", so to speak.
The reference to the cell containing the amplitude, for instance, rather than $B$2, has been written as B$2 instead. That is, the symbol $ has been used to "block" only the 2, that is to say the row index, while the column index has been allowed to vary.
This way, when we'll copy and paste cell B9 to cells C9:F9 (horizontally), the reference will behave as a relative one. As a matter of fact, when dragged horizontally, a relative reference changes only its column index (that is, the letter), while the row index (that is, the number) remains constant. By putting B$2, we have allowed the column index to change (because it is not prefixed by the symbol $).
On the other hand, when we'll drag the formula vertically over the cells that lay below cell B9, the reference will behave as an absolute one. When dragged vertically, indeed, a relative reference changes only its row index (that is, the number), while the column index (that is, the letter) remains fixed. By writing B$2, we have prevented the row index from changing (because of the $ in front of it).
In addition to the references to the cells above, the formula contains also a reference to cell A9. This reference should behave exactly the other way round the three just mentioned:
- when the formulas are copied horizontally, it must not change, because all the points lying, for example, on row 9 (each belonging to a different harmonic component), must reference the time instant contained in cell A9;
- when the formulas are copied vertically, it must change, because each successive value of the same sine wave must refer a corresponding successive instant of time (the formula in B9 must refer to A9, that in B10 to A10, that in B11 to A11, and so on).
For this reason, the reference in A9 has been written as $A9: the column index is blocked, while the row index is allowed to change.
So, let's copy and paste cell B9 now, by dragging it up to F9:
Once you've done this, copy and paste vertically. Select the five cells from B9 to F9 and, after grabbing the selection bottom right corner, drag down to row 1009 (or, otherwise, copy and paste as you've learned in the previous tutorial):
Harmonics' composition
In the previous point, we created the harmonics that, summed up, approximate the signal under consideration (that is, the square wave). Now we only need to make this sum, time instant by time instant. Namely, for each row, we will compute the sum of the values of all the sinusoidal components lying on the same row.
To compute the sum at time 0, in cell G9 enter:
(the expression B9:F9 means: "from cell B9 to cell F9", that is "all the cells between b9 and F9")
Copy the formula by dragging it up to cell G1009 and add a column header, writing "Sum" in cell G8:
Plot the harmonic components
We want to create two charts, the one representing all the harmonic components, the other showing the signal obtained by adding them up. Let's start with the first.
It is useful to write at row 8 a header for each column, so that Calc will be able to use that header in the chart's legend:
Now, select the range A8:F1009 by dragging the mouse or using the following method:
- select cell A8 by clicking on it,
- scroll the sheet down to row 1009 without dragging, that is without holding the mouse key,
- press SHIFT on the keyboard,
- click on cell F1009.
Click the "Chart" button, or, if not visible, click "Chart..." from "Insert" menu.
A window named "Chart wizard" appears. Click and fill the fields as in the following figure:
In step 2 of the chart wizard, verify that "First row as label" is checked.
Keep on clicking "Next" until you reach step 4. Here, in the "Title" textbox, enter "Harmonic components' plots" and click "Finish".
Here is the chart you should have created:
Format the axes t numbers as explained in the previous tutorial, so that they're represented with only one decimal place.
In this same window, choose the "Scale" tab and set manually the maximum value to 1:
If everything is correct, the chart should look like this:
The plots you see - I'd like to stress it - are the sine waves that, summed up, best approximate a square wave of amplitude 1 and frequency 5 Hz.
Before stepping forward to the next point, move the chart where you prefer.
Create a chart for the sum of the harmonic components
You should have noted at the previous step that Calc has been able to automatically detect which data to use for the horizontal axes and which for the vertical one.
In order to get the same result now, we should first select the columns A and G. We could do it, but we prefer to use a different method, which will give us the opportunity to learn how to tell Calc which data it must use for the horizontal axes.
Let's then select the data in column G:
Now start the chart wizard by clicking the corresponding icon and select the same options as in the previous point up to step 3.
At step 3, as we expected, while "Y-Values" contains the data we have selected before starting the wizard (namely the range G9:G1009), "X-Values" field is empty. What we need to do is fill it manually.
To prevent copying errors, follow these steps:
- select "Y-Values" in the box "Data ranges";
- copy the content of the textbox "Range for Y-Values" (which should be $Sheet1.$G$9:$G$1009, provided you haven't changed the worksheet's name);
- select "X-Values" in the box "Data ranges";
- paste in the textbox "Range for X-Values" what you have just copied;
- in the expression you have just copied in the textbox "Range for X-Values", replace with a letter A each one of the two letters G.
This is what you should see:
We have copied, pasted and modified an expression which is worth some explanation:
$Sheet1.$A$9:$A$1009
You should already be able to understand the part $A$9:$A$1009, which represents all the cells between cell A9 and cell A1009. What comes before that, namely $Sheet1., means that that range of cells is taken from Sheet1. It's just a more complete and less ambiguous way to specify the range, since an analogous range A9:A1009 exists also in all the other worksheets of our file (Sheet2, Sheet3 and other possibly present).
The syntax is important, so we summarize it here:
- the colon, that is the symbol :, is used to represent a cell range, by specifying the first and the last cells of the range;
- the dot, that is the symbol ., is used to separate the indication of the sheet from that of the cell range within it;
- as for the symbol $, you should already know that it is used to "block" a row or column reference; in the present case, its presence is irrilevant (try to remove it, and you'll find that nothing changes).
Click "Next" and, at step 4, insert a title (for instance "Sum of the harmonic components") and uncheck "Display legend". Then click "Finish".
Now format the x-axes like you did in the previous chart by right-clicking the axes, selecting "Format Axes..." and setting the appropriate values in the tabs "Scale" and "Numbers".
The chart should now look like this:
As you my notice, even though the plot resembles a square wave, we could certainly approximate it better:
- the "ripple" in the part that should be "flat" is quite large (letter "A" in the figure below);
- the slopes that connect the positive half-waves with the negative ones and viceversa are too gentle (letter "B" in the figure below).
Add more harmonics components
Our square wave could be better approximated if we add more harmonic components. Let's start adding two of them.
As you may have noticed, even if we haven't told it explicitly, only the odd components are non-zero. The even ones, indeed, do not contribute at all (see columns C and E).
If we want to add two more non-zero components, then, we must get to the nineth harmonic.
In order to make room for the harmonics we want to add, right-click on column G header and select "Insert columns" in the context menu that pops up:
Repeat this same action three more times, until the column "Sum" gets to column K. Then insert the headings at row 8 of the columns just added. The sheet should look like this now:
Now we must fill the table at the top of our sheet with the values for the new harmonics that we want to add. It's therefore the moment to replace, at row 2, the values that we inserted "by hand" at the beginning of this tutorial with the formula for computing the amplitudes automatically.
The "mysterious" numbers that we inserted (namely 1.273, 0.424, 0.255), come from the following espression, which holds for the generic harmonic n (for n odd):
Please remember that the formula applies only to n odd, while the amplitude of even harmonics is zero.
Now try to compute the previous expression for n=1, n=3 and n=5 and you will obtain the values 1.273, 0.424 e 0.255.
To make Calc compute the amplitude of the harmonics, what we need to do is writing the previous expression in cell B2 and then copying it along row 2. So, in cell B2 write:
=4/(B1*PI())
Now grab the bottom right corner of the cell and drag right up to column J. There's only a slight problem: the expression, as we have already said, holds only for odd order harmonics; therefore, you should replace with a 0 all the amplitude values that have been computed by the formula for even values of n (namely those in the columns C, E, G, I).
There's a smarter way, though, to get to this, even if a little effort is needed to write the formula. We're going to use two functions that may be new to you: the IF function and the ISODD function.
IF function
This function receives three arguments:
IF ( test ; expression1 ; expression2 )
where:
- test is a condition, whose outcome can be either true or false;
- expression1 is an expression, that is, a number, a date, a text string, or even a complex expression made up of operands, operators and functions, which is computed if the test condition results true;
- expression2 is the expression that is computed if the test condition results false.
As an example, let's suppose that cell C4 contains the grade of a certain student in a certain subject. Then, putting the following formula in cell D4
= IF ( C4<6 ; "FAILING" ; "GOOD" )
results in the string "FAILING" showing up in D4 if the value in C4 is less than 6; it results in "GOOD" in cell C4 otherwise.
ISODD function
This function returns true if the integer value passed to it as an argument is odd; it returns false otherwise.
By combining the two functions just presented, we can compute the value of each harmonic component telling apart the two cases of odd order harmonic (having non-zero amplitude) and even order harmonic (with zero amplitude). Here is the formula to be put in cell B2:
=IF(ISODD(B1);4/(B1*PI()))
Which can be read as: if the content of B1 is odd, then compute 4/(B1*PI()), otherwise give 0 as the result.
Now copy and paste that formula in the cells from C2 to J2. Then copy and paste the formula to compute each point of the sine waves to fill the columns G, H, I, J up two row 1009. Where shall you copy this formula from?
No matter which one of the cells in the range B9:F1009 you choose, the formula will be adapted correctly thanks to the combination of "blocking" and "non-blocking" references we have put into it. So, choose one of the cells in B9:F1009, copy it and paste in the range G9:J1009. When you're done with this, update the formulas of column K to make the sum span over all the harmonic components.
The chart will update automatically becoming like this:
The approximation has already improved a lot. Anyway, I've tryed to do better than that, by adding harmonics up to the 25th order!
This is what I have obtained:
...Would you like to try it yourself?
