working out the frequency of a periodic function

[nasal cherry]

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

 

[JackDanIce]

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:

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]

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
	A	B	C	D	E	F	G	H	I
26	1	amp	1	2	3
27	2	freq	1	3	5
28	90	SHIFT	0	90	180
29	Degrees	SumWave	Wave1	Wave2	Wave3	TIME	FFTfreq	FFTamp	FFTcomplex
30	0	-2	0	-2	1.84E-15	0	0	0	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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

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
	G	H
29	FFTfreq	FFTamp
30	0	0
31	1	1
32	2	0
33	3	2
34	4	0
35	5	3
36	6	0
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]

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.