working out the frequency of a periodic function

nasal cherry

New Member
Joined
Jul 28, 2008
Messages
2
Hi

i have some data that shows a sawtooth like periodic shape with time.Column a is the time, hh:mm:ss.00, column b is the data, with one point every second. The data increases for about a minute, then drops back to its original value
I would like to work out the frequency of this sawtooth function. I get the feeling this is not easy, but if anyone has any ideas please let me know! I was thinking of working out the time at which the max and min of the function ocurrs over a minute, then doing this for the next minute, then taking an average. this would give an average period, and Freq= 1/Period me thinks... but i'm not sure how to do this!!

cheers for any help

NasalC
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
I worked on something similar to this ages ago, macro below requires time to be in column A and observations in column B. Run it and see what happens and see if you can then modify it to what you want:

Code:
Sub Scan_Identify_Calculate_Difference()
Dim i As Integer, j As Integer
Dim switch As Boolean
 
i = 2
switch = True
Do
If switch = True Then
    If Cells(i, 2) <= Cells(i + 1, 2) Then
        i = i + 1
    Else
        Cells(i, 3) = "Peak"
        switch = False
        i = i + 1
    End If
Else
    If Cells(i, 2) >= Cells(i + 1, 2) Then
        i = i + 1
    Else
        Cells(i, 3) = "Trough"
        switch = True
        i = i + 1
    End If
End If
Loop Until IsEmpty(Cells(i, 2))
Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.Sort Key1:=Range("G1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
 
Range("E1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("G1:H1").Select
Selection.Font.Bold = True
Range("G1").Value = "Peak/Trough"
Range("H1").Value = "Difference"
Columns("H:H").Select
Selection.NumberFormat = "#,##0.000_ ;[Red]-#,##0.000"
Range("G2").Select
j = 2
Do
    If Cells(j, 7) = Cells(j + 1, 7) Then
        Cells(j + 1, 8) = Cells(j + 1, 6) - Cells(j, 6)
        j = j + 1
    Else
        j = j + 1
    End If
 
    If IsEmpty(Cells(j + 1, 7)) Then
        Cells(j, 8) = Cells(j, 6)
    End If
Loop Until IsEmpty(Cells(j, 7))
Columns("E:H").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Selection.EntireColumn.AutoFit
 
Range("A1").Select
End Sub
 

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
I have not tried the solution by JackDanIce, but I would guess it will only work if there is only one basic frequency.
If there is more than one overlapping component wave making up the signal, the Excel Fast Fourier Transform (FFT) in the data analysis toolpack can be used, with some limitations as below.
The number of data points must be a power of 2.
In the example below, 512 (2 to the ninth) data points are used.
The example uses one SumWave which is made up of 3 component waves.

In Row 26 is the relative amplitudes of the component waves; 1, 2, 3.
In Row 27 is the relative frequencies of the component waves; 1, 3, 5.
In Row 28 is the relative phase of the component waves; 0, 90 and 180 degrees (this makes no difference to the result).
The formulas in A30 to H30 are copied down through Row 541

In Columns starting in Row 30 to Row 541:
A -- Degrees for SIN Function
B -- SumWave sum of component wave functions:
C, D, E -- in columns C, D, E
F -- Time which is one unit, call it 1 minute corresponding to the 360 degrees or one radian
G -- FFTfreq (FREQUENCY)
H -- FFTamp (AMPLITUDE)
I -- FFTcomplex (resulting complex number) installed by sheet change macro code in Sheet1 code module (see code below)
Excel Workbook
ABCDEFGHI
261amp123
272freq135
2890SHIFT090180
29DegreesSumWaveWave1Wave2Wave3TIMEFFTfreqFFTampFFTcomplex
300-20-21.84E-150000
Sheet1
Excel 2003
Cell Formulas
RangeFormula
D26=C26*(1+A26)
D27=$A27+$C27
D28=$A28+$C28
D30=SIN(($A30+D$28)*PI()/180*D$27)*D$26
E26=C26*(1+2*A26)
E27=$A27*2+$C27
E28=$A28*2+$C28
E30=SIN(($A30+E$28)*PI()/180*E$27)*E$26
A30=(ROW()-30)/512*360
B30=SUM(C30:E30)
C30=SIN(($A30+C$28)*PI()/180*C$27)*C$26
F30=A30/360
G30=ROW()-30
H30=IMABS(I30)*2/512


Code:
Option Explicit
Dim ActyveCel As String, Addry As String, Addrz As String
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERRO1
Addry = 30
Addrz = 541
Application.ScreenUpdating = False
ActyveCel = ActiveCell.Address
If AddIns("Analysis ToolPak").Installed = False Then AddIns("Analysis ToolPak").Installed = True
If AddIns("Analysis ToolPak - VBA").Installed = False Then AddIns("Analysis ToolPak - VBA").Installed = True
With Application
    .EnableEvents = False
    .EnableCancelKey = xlDisabled
    .ScreenUpdating = False
    .Calculation = xlManual
    Range("I" & Addry & ":I" & Addrz).ClearContents
    .Run "ATPVBAEN.XLA!Fourier", ActiveSheet.Range("$B$" & Addry & ":$B$" & Addrz), ActiveSheet.Range("$I$" & Addry & ":$I$" & Addrz), False, False
'This below is the inverse FFT
'    .Run "ATPVBAEN.XLA!Fourier", ActiveSheet.Range("$I$" & Addry & ":$I$" & Addrz), ActiveSheet.Range("$J$" & Addry & ":$J$" & Addrz), True, False
End With
EX1T
Exit Sub
ERRO1:
EX1T
End Sub
Sub EX1T()
With Application
    .Goto Range("A1")
    .Goto Range(ActyveCel)
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    .EnableCancelKey = xlInterrupt
    .EnableEvents = True
End With
On Error GoTo 0
End Sub
The 3 component waves in Columns C to E make up the SumWave input wave in Column B for the FFTcomplex in Column I.
These 3 waves have amplitudes of 1, 2, 3.
These 3 waves have frequencies of 1, 3, 5.
Note that the resulting three peaks below corresponds to these amplitudes and frequencies.
Excel Workbook
GH
29FFTfreqFFTamp
3000
3111
3220
3332
3440
3553
3660
Sheet1

Excel 2003
There can be a line chart for the amplitudes of the 3 component input waves and the SumWave of these 3, on vertical axis, versus Degrees in Column A on the horizontal axis, in the range from Cell A1 to G25.
There can be a scatter chart for the FFTamp in Column H on the vertical axis, and the FFT Complex in Column I on the horizontal axis, in the range from Cell H1 to N25.

No more than half of the points for the scatter plot should be plotted, for FFTfreq from 0 for up to 255, as the other half of the plot will tend to mirror the first half.
 

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
Were you able to apply one of the solutions posted?
I did not mention that the FFT also works when there is only one underlying wave.
This below shows the input and output, with the parameters as previously given, except that the phase shift in Cell A28 is 0.
In the bottom half simulated noise has been added.

 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,856
Messages
5,766,786
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top