Thread: Multiple peak values (max/min)

Hi
I'm wondering if there is an easy way of finding multiple max or min values (peaks) on a graph (data set)

Please see the attached image. I would like to know the value of each of the 10 peaks (marked with red dots) to average the value. I've got loads of graphs like this that needs the same averaging of peak values, and was just wondering if there is such a function in excel instead of looking through each data set and manually finding each of the 10 peak values.

I'ts not that simple because peaks aren't uniformy defined.

Can you upload example worksheet?
If you have sensitive data copy->paste as values values into new workbook and upload.

Basically if your source values were listed say in Column A row 2 onwards then in B you could use something like:

Code:

B2: =IF(AND(A2>A1,A2>A3),A2,"")
copied down

You can then simply obtain your result using:

Code:

=AVERAGE(B:B)

But there are some peaks that are excluded

Good point zbor... in which case yes as you state we will need more info - specifically in regard to the thresholds that govern local max (ie % fluctuation etc...) ... sorry I missed that point.

I would rather go over Y=6 (must see how to get that value),

and look for max in intervals where Y=6 intercept with function.

But would be easier if I get inputs

Or with min's wich are more emphased than max's

Edit: removed formula as it addressed "peak" values in the data but not for the chart peaks.

Originally Posted by WHER

Maybe a bit simplistic, but a possible approach.

Maybe simplistic, but nevertheless a big help. At least it makes it much easier to scroll through the data set to find the peak values. Thank you very much.

Still open for more suggestions if you guys and girls got any.

EDIT: Just so you people don't misunderstand me (referring to Palmettos edited post). I don't need to find and mark the peak values on the chart/graph it self, as long as I can extract/get the peak values from the data set I'm fine. The chart/graph was just to give you an idea of what kind of peaks I was talking about.

I'm stuck.

I think you need to take =ATAN() from your numbers to get graph like this:

max_min(2).xls

From this diagram it's easier to take minimums (where you must define what's minimum).

untitled.JPG

It's something you need to define manualy (let say in cell A1)
Therefore you find is max/min>A1 -> that's peak.

Like it said in example: is that one peak or 2? (you said that's 2 but someowhere need to be defined).

But what I don't know how to do is when you find first minimum, set 0 (look sheet 2, I set it manually) and go to find second minimum and set again 0.

Those 0's are crucial. After you define them you can find max and average within those intervals wich again I don't know how to set.

But I hope this will give someone idea how to do that.

I kinda figured it wouldn't be easy Really appreciate the effort.

If I manually have to define my intervals that kinda defeats the purpose, that's basically what I want to avoid.

I've attached a picture that shows the peaks I'm talking about. There are 10 max peaks (red) and 9 min peaks (green) that I'm interested in, the 10 max and 9 min values within those squares (regardless if there are 2 or 3 peaks within one of those squares, I just want the one and only max and min value)

But when thinking of it it seems very complicated. I guess one had to create a check to see if the previous number was higher (for the max peaks) or lower (for the min peaks) than the previous number, but that again would give me almost twice the amount of peak values in return that I was looking for since there are basically two peaks within the peak (square box) I'm looking for (poor explanation, made sense in my head)

I think WHER's suggestion was very good. A minor variation would give minima instead of maxima, or both.

The aperture size could be put in a single cell and changed, while observing the plot, to get it right for the data (assuming the data has some periodicity, as shown in the example).

For example, define

conAper refers to: =$C$1

rgnAper Refers to: =INDEX(Sheet1!$A:$A, ROW() - conAper / 2):INDEX(Sheet1!$A:$A, ROW() + conAper / 2)

... and WHER's formula to

=IF(OR(A51=MAX(rgnAper), A51=MIN(rgnAper)), A51, NA())

Then adjust C1.