Find Major Lows and Highs in Series

[Nick_London]

Hi,

I have a series in column G. Using the formulas below I am using them to extract all the lowest and highest points in a series in columns H and I.

Column I = Lows

=IF(AND(G9<G8,G9<G10),G9,0)

Column H Highs

=IF(AND(G9>G8,G9>G10),G9,0)

However, because the series as many mini troughs and peaks, too many points are being returned, Now I would like to show only the main troughs and peaks rather than all of them either using the new data or the original series. Is it anyway possible to do this - just extract the "higher level" highs and lows instead of all them.

I was thinking along the lines of creating two new series by applying a similar formula to columns H and I. Could this be done?

Alternatively, maybe what's needed is to define exactly what would constitute a major peak and trough. For example, only bring back the lows that diverge at least by 10% from the previous peak and vice versa.

Hope some can advise. I'm open to spreadsheet or VBA solution.

Thanks,

Nick

[Bob Phillips]

Nick,

I am not clear as to why you are not using Min and Max to simply get those numbers.

Can you layout some data so we can get the idea?

[MattKlein]

Hi Nick,

Try this sub
[vba]
Sub CleanUp()
Dim MaximaCol As Integer, MinimaCol As Integer
Dim StartRow As Long, EndRow As Long
Dim Index As Long, SearchIndex As Long
Dim Threshold As Single

MaximaCol = 8 'H
MinimaCol = 9 'I

'Fill in your series start and end row
StartRow = 2
EndRow = 100

'how much must the difference between minima and maxima be?
Threshold = 0.5

'We'll do bottom-up, just for fun
For Index = EndRow - 1 To StartRow + 1 Step -1
If Cells(Index, MaximaCol).Value <> 0 Then
'find last minima
For SearchIndex = Index - 1 To StartRow + 1 Step -1
If Cells(SearchIndex, MinimaCol).Value <> 0 Then
If Cells(Index, MaximaCol).Value - Cells(SearchIndex, MinimaCol) < Threshold Then
'cancel out the min/max pair
Cells(Index, MaximaCol).Value = 0
Cells(SearchIndex, MinimaCol).Value = 0
End If
'go for a little optimization, pun intended =p
Index = SearchIndex
Exit For
End If
Next
End If
Next
End Sub
[/vba]

This should give you a starting point.

-Matt

[Nick_London]

Hi,

Thanks for the code Matt. Actually thinking about this further, not sure if this code gives me exactly what I need. In response to XLD's comments let me explain more.

In the attached workbook, I have worked out the lows and highs for the given data in column B as per the formulas provided. But the formulas are returning too many lows and highs, I only want to identify major ones or instances where the spread is greatest.

For example, in the graph, the red bars represents these lows, However I only want to bring back the most significant lows/troughs, i.e the points that I have circled.

But now having thought about this in quite a lot of depth I thinking my approach maybe wrong. Instead of using previous troughs/low as my point of reference for identify the next trough, I should be using peaks instead or more specifically the Peak to Trough variance to get the points in the circles.

Does this make sense? But I haven't got a clue how you'd go about doing that. I've tried various combinations but no luck.

Nick

[Bob Phillips]

You need some hard and fast business rules if you want a solution, otherwise we could be here forever trying to second-guess the best algorithm.

For instance, a peak to trough gap, is that the previous peak, or a peak prior to that if it was coming down, went up, and then came down again (as in 70-80). What is the peak in a range of peaks?

[Nick_London]

Hi,

Well I guess in terms of what I want to do, at the simplest level, ideally:

My lows/troughs would be - any low that is at least say 5% lower than the subsquent peak.

And the highs/peaks would be any high that is at least 5% above the subsequent low.

So kinda working backwards

But don't know if that's enougth information to move forward.

Nick

[mikerickson]

Could you post some sample data along with which peaks you want caught?

Is the goal to identify peaks in data as opposed to peaks in the background sample noise?
To choose a peak filter, are you looking for the max/min of a smooth wave or are you looking for sharp peaks that stand out from a smooth "best fit" curve.

[MWE]

Hi,

Well I guess in terms of what I want to do, at the simplest level, ideally:

My lows/troughs would be - any low that is at least say 5% lower than the subsquent peak.

And the highs/peaks would be any high that is at least 5% above the subsequent low.

So kinda working backwards

But don't know if that's enougth information to move forward.

Nick

it sounds like you seek "peak detection" algorithms; these are pretty common in surface metrology where a surace is scanned and peaks and valleys detected in an attempt to understand "surface roughness". There are literally dozens of schemes that have been proposed over the years. I actually wrote quite a few in the early 1980s when I was doing some work for a metrology group at Eastman Kodak (you can imagine their interest in surface roughness for large rollers used in the manufacture of photographic film -- remember that stuff?). The basic schemes were based on rule sets like "at least n points lower on each side" for a local peak, or percentage rules like you outlined above; but virtually any rule had to be adjusted for real world funny stuff like plateaus. Often, several automated passes were required to get some feel for the data and then adjust the rules to get desired results (what the eye sees). I will see what I can dig up.

[Bob Phillips]

I have a pretty rough and ready algorithm, nothing as sophisticated as MWE refers to, but even so it is skewed by the start position. Looking at your data, if I have a start low and high of the first point, nothing happens ungtil point 38, which seems to agree with your first circle, then we get quite a few in a rush as we are dropping, but the previous high is the same.

Want to see it?

[Nick_London]

Hi XLD,

Sure, would like to see it.

Thanks,

Nick

[Bob Phillips]

Okay here goes.

C3: =B3
D3: =B3

C4: =IF($B4<INDEX($D$3:$D3,MAX(IF($D$3:$D3<>"",ROW($D$3:$D3)-ROW($D$3)+1)))*95%,$B4,"")
D4: =IF($B4>INDEX($C$3:$C3,MAX(IF($C$3:$C3<>"",ROW($C$3:$C3)-ROW($C$3)+1)))*105%,$B4,"")

C4 & D4 are array formulae, so Ctl-Shift-Enter them.

Then just copy C4:D4 down as far as you need.

[Nick_London]

Hi XLD,

Thanks for the formulas, I've tried this out but it's returning data for too many troughs and peaks, against the objective of reducing the number return and captuaring just the major ones.

For thoose who've joined the debate later (MWE, MikeRickson) I'm providing more data and indication of the types of peaks and troughs I seek to capture.

In the attached spreadsheet, I've circled the types of peaks and troughs that I would ideally like to capture and the current highs and lows my formula is returning.

Thanks,

Nick

[Bob Phillips]

Then you need another rule to determine which don't get included as I think mine met your original rule.

BTW, your first trugh is 94.954% of the previous peak, below your 95% threshold.

[Nick_London]

XLD,

Have to admit I didn't measure the divergence when circling the points, just did it visually!

Anyway, I've played around a bit more and this is what I've come up with (sheet new solutions) to capture the trougths. All workings are in the spreadsheet. It's not perfect but it does capture 6 of the ten lows I circled and only returns 7 points.

Have not done the calculations for the peaks yet. If anyone can think of how I could improve this at all further that would be great.

Thanks

Nick