Locating multiple maxima within a data column

Can anyone help me with a formula that will return a list of maximum values within a column of data. For instance if I plot a sine curve type shape with a frequency of 1 Hz over a 10 second period there will be 10 maxima which I want returned. When I use the =MAX function this simply returns the highest single value.

Many thanks in advance

Alan

Hi,

Check "=Large" and "=Small" functions

Thanks for that but it isn't really what I am after. I have some lengthy time series which have multiple peaks values, which are all each of different value. I am unsure of how many max events there are and want these values rather than just the nth highest - which may be associated with just before or after a particularly high maximum rather than the value of a lower maximum in the series.

Al

The solution I usually see to a problem like this is usually not well received -- maybe because it involves more calculus than most are comfortable with. In a calculus class, this sort of "where are the maxima/minima" problem reduces down to a problem of finding the roots/zeros of the 1st derivative. So, the problem is solved using an approach like this:

1) calculate the 1st derivative. For something as easy as the sin() function, this is just the cos() function. For more elaborate functions, the expression for the derivative will be more difficult, and, if you only have a table of values (rather than a function), you may need to resort to a numeric derivative.
2) Set derivative equal to 0. You may be able to do this algebraically in some cases. Others will require numerical methods (like the bisection method or the Newton Raphson method) to locate the 0's.
3) Since you only want maxima and not minima, you will need a quick test to see if this is a maxima or minima.

That is exactly what I was looking for - many thanks!

Hi Alan and MrShorty,

This is all above my head....just dont understand what was the problem and the solution.

NEways, sorry for creating confusion.