The previous sections of the tutorial handled the basic formulas behind building a Fourier model and creating a set of input functions.
This section deals with formula implementation on the spreadsheet, the brief VBA code and the charting of the Fourier transform components.
[sociallocker]
[/sociallocker]
A Fourier Transform Model in Excel #3
by George Lungu
– This is a tutorial about the implementation
of a Fourier transform in Excel. The first part
went over adjustments that make the general
Fourier transform formula applicable on real
time sampled signals with a finite number of
samples.
– This second part dealt with the Excel
implementation of the input signals.
– This third part is concerned with
implementation of the Fourier transform
formulas (real, imaginary, magnitude and
phase), the structure of the macros driving the
model and the charting of the Fourier
transform components
<excelunusual.com> by George Lungu
A quick review of the formulas:
The Fourier transform formula derived in the first section of the tutorial is:
Real part – Re(G(f)) Imaginary part – Im(G(f))
N
Re G(f ) h g(nh)cos(2 f nh)
Underlined with green are quantities already existent in the worksheet. Underlined with red are
quantities needed to be further calculated in the worksheet.
– “h” is an input parameter situated in cell B11, N is 4999 and it is the height of the calculation tables
– t = nh is the sampled time series and it is located in range A41:A5040
– g(nh) for n=0,1,2,…4999 is the sampled real input function and it’s located in range B41:B5040
Implementation of the active formulas :
We will set up the spreadsheet to calculate the real and
imaginary parts of the FF for only one frequency point, then using a special macro we will change the
frequency incrementally in a loop and record the calculated Fourier transform in each of the frequency points
from the start frequency to the end frequency.
<excelunusual.com> 2
-Recognizing the fact that we need to estimate the Fourier transform in a series of frequency points we have to
replace the frequency “f” with a sampled series of frequencies.
– Since we set up the initial conditions to contain fstart, , fstop and the number of frequency points (#points), the
frequency can be expressed as: f =fstart + m*Df, where Df = (fstop – fstart,)/(#points) and m is the index# (cell B4).
– In this case the real and imaginary parts of the Fourier transform become:
Let’s see how to calculate the remaining parts in the formulas (red underline):
The frequency will be calculated in cell E41: “=B1+B4*(B2-B1)/B3”
this value will be calculated for n=0,1,2,…, 4999 in range C41:C5040;
– cell C41: “=B41*COS(2*PI()*E$41*A41)” copy C41 down to C5040
this value will be calculated for n=0,1,2,…, 4999 in range D41:D5040;
– cell D41: “=B41*SIN(2*PI()*E$41*A41)” copy D41 down to D5040
this value will be calculated in cell F41: “=B5*SUM(C41:C5040)”
this value will be calculated in cell G41: “=-B5*SUM(D41:D5040)”
<excelunusual.com> 3
Let’s calculate the amplitude and the phase of the Fourier transform:
this value will be calculated in cell H41: “=SQRT(F41^2+G41^2)”
Phase G( f ) atan value calculated degrees in cell I41: “=180*ATAN(G41/F41)/PI()”
Overview of the calculation area:
– Above, there is a snapshot of the spreadsheet calculation area discussed so far.
– Range E41:I41 contains the final Fourier formulas. Everything below (range E42:I5040) will be filled with
information from range E41:I41 by a copy-paste type of macro. A reset macro will clear range E42:I5040.
<excelunusual.com> 4
The “Reset()” and “Calculate_()” macros: Dim s As Boolean
————————————————
Sub Reset()
– The “Reset()” macro clears all the information from any previous calculation which is in range E42:I5040. The macro also resets the index number.
[B4] = 0
[E42:I5040].Clear
End Sub
– The worksheet computes the Fourier transform for a single frequency points that would have resulted into an excessively index number.
————————————————
Sub Calculate_()
x
s = Not (s)
hundred
Do While s = True And [B4] < [B3]
large file.
DoEvents
[B4] = [B4] + 1
– A good compromise is to have the “Calculate_()” macro change the frequency from start to end in small increments and save the results in a table of constants. This way we have just a few cell with formulas and [E42:I2040] = [E41:I2039].Value
the file is kept at a reasonable size. The only problem with this method is that we trade file size for speed.
– Boolean variable “s” has the role of allowing the same button to be used for starting and pausing the macro.
– The “Calculate_()” macro has a loop which achieve two purposes. The first is to take the active calculation
results and store them as constants in a table (it does this by a copy-paste operation). The second purpose is to
increment the index thus allowing the worksheet to calculate a Fourier function for a new updated frequency
point during every loop cycle.
– The “Do” loop is a conditional loop and it can run only if s = True and the index is smaller than the maximum
number of points specified in the input parameter area. This achieves two goals, one is that the macro can be
started and paused from the same button, and the other is that the macro will run the calculation through the
number of frequency points specified and then stop. A new run is then possible only after a reset.
<excelunusual.com> 5
Charting the Fourier transform components:
-Chart the input function of time on a 2D
scatter chart:
X-Values Range: A41:A5040, Y-Values Range:
B41:B5040
– Chart the amplitude part of the Fourier
transform function of frequency on a 2D
scatter chart:
X-Values Range: E41:E5040, Y-Values Range:
H41:H5040
– Chart the phase part of the Fourier transform
function of frequency on a 2D scatter chart:
X-Values Range: E41:E5040, Y-Values Range:
I41:I5040
– Chart the real part of the Fourier transform
function of frequency on a 2D scatter chart:
X-Values Range: E41:E5040, Y-Values Range:
F41:F5040
– Chart the imaginary part of the Fourier
transform function of frequency on a 2D
scatter chart:
X-Values Range: E41:E5040, Y-Values Range:
G41:G5040
to be continued…
by George Lungu <excelunusual.com> 6
