Spectral Analysis Using

Excel
P. Tran
John Tyler Community College
 Overview
Calculating Fourier transform using Excel.

Spectral analysis of sound from a guitar.

 Applications of Fourier Transform
Signal processing

Quantum mechanics: solving the time-dependent Schrödinger equation

Evolution of a Wave Packet in a 1D Potential
𝜕𝜓
𝑖ℏ = 𝐻𝜓
𝜕𝑡

(ℏ𝑘)2
𝐻= + 𝑉(𝑥)
2𝑚
𝐻
−𝑖 𝑡
𝜓 𝑥, 𝑡 = 𝑒 ℏ 𝜓(𝑥, 0)

𝑖 ∆𝑡 𝑖 ℏ𝑘 2 𝑖 ∆𝑡
− 𝑉(𝑥) 2 − 2𝑚 ∆𝑡 − 𝑉(𝑥) 2
𝜓 𝑥, Δ𝑡 ≅ 𝑒 ℏ 𝑒 ℏ 𝑒 ℏ 𝜓(𝑥, 0)
 Evolution of a Wave Packet in a 1D Potential
𝑖 ∆𝑡 𝑖 ℏ𝑘 2 𝑖 ∆𝑡
− 𝑉(𝑥) 2 − 2𝑚 ∆𝑡 − 𝑉(𝑥) 2
𝜓 𝑥, Δ𝑡 ≅ 𝑒 ℏ 𝑒 ℏ 𝑒 ℏ 𝜓(𝑥, 0)

𝑖 ∆𝑡
− 𝑉(𝑥) 2
Diagonal in real space 𝜓1 𝑥, Δ𝑡 = 𝑒 ℏ 𝜓(𝑥, 0)

𝑖 ℏ𝑘 2
Diagonal in k space 𝜓2 𝑥, Δ𝑡 = 𝑒 −ℏ 2𝑚 ∆𝑡 𝜓1 𝑥, Δ𝑡

𝑖 ∆𝑡
− 𝑉(𝑥)
Diagonal in real space 𝜓 𝑥, Δ𝑡 = 𝑒 ℏ 2 𝜓2 𝑥, Δ𝑡
 Why Excel?

Want a computing platform that all students can have access to.
 Nuts and Bolts

𝑓 𝜔𝑖 = න 𝑓(𝑡)𝑒 −𝑖𝜔𝑖 𝑡 𝑑𝑡

Don’t calculate all components at once: too much typing.

Use iterative option of Excel.

Calculate the ith component during the ith iteration. Save for use later.
 Data Data × window

ti Window cos (nti)

sin (nti)
 Be Careful
Excel calculates from left to right and top down for each iteration.

So make sure instructions are in the correct order.

 Not FFT
Can program to do FFT.

But too complex, and the gain in computation time for the data size is not worth it.
 Tuning Fork (384 Hz)
384.8Hz
F(t) F(w)
1.20 0.0800
1.00 0.0700
0.80
0.0600
0.60
0.0500
0.40

F(w)
0.20 F(t) 0.0400
F(t)

0.00 Window W(t) 0.0300

-0.20 0 200 400 600 800 1000 1200
F(t)W(t) 0.0200
-0.40
0.0100
-0.60
-0.80 0.0000
0 100 200 300 400 500 600
-1.00
Index Index
 Usual Standing Wave Experiment

Wouldn’t it be more interesting to do an experiment with a musical instrument?

 Standing Wave on a Guitar
Fundamental 3rd harmonic
2nd harmonic 4th harmonic
F(t) F(w)
2.50 0.0500
0.0450
2.00
0.0400
1.50 0.0350
1.00 0.0300

F(w)
F(t) 0.0250
F(t)

0.50 0.0200
Window W(t)
0.00 0.0150
F(t)W(t)
0 200 400 600 800 1000 1200 0.0100
-0.50
0.0050
-1.00 0.0000
0 100 200 300 400 500 600
-1.50
Index Index
Frequency vs 1/Length
Fundamental frequency v 1/length
140

130
y = 56x - 4.5

Frequency (Hz)
120 R² = 1.0
110

100

90

80
1.5 1.6 1.7 1.8 1.9 2 2.1 2.2 2.3 2.4
1/L (m-1)
Frequency vs Wave Speed

Frequency vs Wave Speed

140

120

100

Frequency (Hz)
80
y = 0.94x - 17
60
R² = 0.99
40

20

0
100 110 120 130 140 150
Speed (m/s)
 Strength vs Time
Strength vs time
60

50 3rd harmonic

40

Strength
30

20 2nd harmonic

10
4th harmonic
Fundamental
0
0 0.1 0.2 0.3 0.4 0.5
Time (s)
 What Else?

Pluck the string at different positions to see how that affects the composition of the
different harmonics.
 Benefits Over Old Experiment
Students can see how physics works in a real life application.

Teach the principle of superposition of waves.

 Difficulty
We only have one guitar.

Solution: ask students to bring in their own if they have one.

Thank you