Peak detection

Question

I have anfile with colums of data. The colums have numbers such that the numbers start to increase and reach a peak from where they start decreasing to a low value which again starts to increase and so on. I want to develop a macro such that it can pull out all the peak values and list it in a separate column and all the lowest values in another column in the same order. Please let me know how I can do that? I have also attached an image of thesheet with a single column of data.

Raju

Answer

Raju,

You don't really need a macro. You can use formulas:

assume your first number is in cell A2

then in B3 put

=if(And(A2<A3,A4<A3),A3,"")

in C3 put

=if(And(A2>A3,A4>A3),A3,"")

now select B3:C3 and drag fill down the column to the next to the last row where you have data.

if you wanted a macro

Sub Addformulas() Dim r As Range Set r = Range("A3", Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)) r.Offset(0, 1).Formula = "=if(And(A2<A3,A4<A3),A3,"""")" r.Offset(0, 2).Formula = "=if(And(A2>A3,A4>A3),A3,"""")" End Sub