I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
=IF(AND(C4>C3,C4>C5),"Local maxima","")But the trouble with this formula is that if the peak stretches across multiple rows it won't catch that as local maxima. And that is despite the fact that it is a local maxima as values begins to fall.
(If you want to know why it is to find the local maxima in a set of spectrum data)
2 Answers
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels() Dim i, myCount, pt ActiveSheet.ChartObjects("myChart").Activate myCount = ActiveChart.SeriesCollection(1).Points.Count For i = 1 To myCount ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value Next i
End SubAnother solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
