vba - How to extract and plot only minimal and maximal peaks of an array , -graph analysis- With Matlab or excel

Question

I'm doing video analysis.
The end result array I get is something like:

signal =

    Columns 1 through 7

       73960       73960       73960       73960       68102       68102       68102

  Columns 8 through 14

       68102       19187       19187       19187       19187       14664       14664

  Columns 15 through 21

       14664       14664       13715       13715       13715       13715       30832

  Columns 22 through 28

       30832       30832       30832       53031       53031       53031       53031

  Columns 29 through 35

       56897       56897       56897       16104       16104       16104       16104

  Columns 36 through 42

       15188       15188       15188       15188       13973       13973       13973

Note: actual array I get is usually 600+

So when I plot this I get very bad looking graph, so I want to filter this array and only keep the ** maximal and minimal peaks** local maxima and minima so that the graph would have nicer waves

Is there a way I can do it with MATLAB?

if not then can I do it with excel? as I usually save this array to excel sheet Like this

0.1 68102
0.15    19187
0.2 14664
0.25    13715
0.3 30832
0.35    53031
0.4 56897
0.45    16104
0.5 15188
0.55    13973
0.6 21437
0.65    66950
0.7 65356
0.75    22562
0.8 14154
0.85    13938
0.9 20692
0.95    72823
1   69975
1.05    15328
1.1 14494
1.15    13681
1.2 14205
1.25    65278
1.3 63055
1.35    16999
1.4 14050
1.45    14245

In which the 1st column is the time(y-axis) and 2nd column is the amplitude.(x-axis)

I use this formula to count local maxima (Thanks to brettdj from stackoverflow.com)

=SUMPRODUCT(--(B2:B149>B1:B148),--(B2:B149>B3:B150))

And this formula to count local minima

=SUMPRODUCT(--(B2:B149<B1:B148),--(B2:B149<B3:B150))

But what I need is to filter the array to only keep local maxima and local minima so I can get nicely drawn curve without noise.

Answer 1

This VBA

• Reads the data from column A and B into a variant arrays
• Find the local minima and maxima and extracts that to second array
• Creates a brand new chart of the minima/maximia (see image)

     Sub NewGraph()
    Dim X
    Dim Y
    Dim lngRow As Long
    Dim lngCnt As Long
    Dim Chr As ChartObject

    X = Range([a1], Cells(Rows.Count, "b").End(xlUp))
    Y = Application.Transpose(X)

    For lngRow = 2 To UBound(X, 1) - 1
        If X(lngRow, 2) > X(lngRow - 1, 2) Then
            If X(lngRow, 2) > X(lngRow + 1, 2) Then
                lngCnt = lngCnt + 1
                Y(1, lngCnt) = X(lngRow, 1)
                Y(2, lngCnt) = X(lngRow, 2)
            End If
        Else
            If X(lngRow, 2) < X(lngRow + 1, 2) Then
                lngCnt = lngCnt + 1
                Y(1, lngCnt) = X(lngRow, 1)
                Y(2, lngCnt) = X(lngRow, 2)
            End If
        End If
    Next lngRow

    ReDim Preserve Y(1 To 2, 1 To lngCnt)

    Set Chr = ActiveSheet.ChartObjects.Add(250, 175, 275, 200)
    With Chr.Chart

        With .SeriesCollection.NewSeries
            .XValues = Application.Index(Application.Transpose(Y), 0, 1)
            .Values = Application.Index(Application.Transpose(Y), 0, 2)
        End With
        .ChartType = xlXYScatter
    End With

End Sub