6

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

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 Sub

enter image description here

0

Another 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

Example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.