Multiple peak values (max/min)

1. 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.

2. Re: Multiple peak values (max/min)

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

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

3. Re: Multiple peak values (max/min)

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

You can then simply obtain your result using:

4. Re: Multiple peak values (max/min)

But there are some peaks that are excluded

5. Re: Multiple peak values (max/min)

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 &#37; fluctuation etc...) ... sorry I missed that point.

6. Re: Multiple peak values (max/min)

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

7. Re: Multiple peak values (max/min)

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

8. Re: Multiple peak values (max/min)

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

9. Re: Multiple peak values (max/min)

Thanks for the fast reply guys. I've attached a excel sheet with a similar data set if that makes things easier.

10. Re: Multiple peak values (max/min)

Perhaps this link may be the start of a solution. Scroll down and see the links at the bottom of the thread.
Calculate Maximum Point On Curve From Xy Scatter Graph

11. Re: Multiple peak values (max/min)

Maybe a bit simplistic, but a possible approach.

12. Re: Multiple peak values (max/min)

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.

13. Re: Multiple peak values (max/min)

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.

14. Re: Multiple peak values (max/min)

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)

15. Re: Multiple peak values (max/min)

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())

16. Re: Multiple peak values (max/min)

Just playing w/ the problem and first issue is that two of your peaks have two identical values in a row (EX: rows 77 & 78).

So far WHER's approach seems best. I tried similar w/ two added columns to smooth the data... I simply used the max of the value and the two following (first to column A, then to column B), then I compared the value in column A to a max of the 30 cells before and after (starting at row 31). I used 30 because that seems to adequately encompass your double-hump peaks. You could then do the same using min functions to find the local bottoms.

17. Re: Multiple peak values (max/min)

I manage to make peak's areas.

You only need to define Y value that intercept all curves:

2.JPG

See lines:
Blue would have 9 max
Red, what you need, 10
Green also 9.

This you can't avoid: either in this approach or my previous with ATAN() function where you had to define min difference between min and max.
Excel can't know what you consider as max or min unless you tell him.

This is perhapes easiest because you need to put that number approximately in yellow box at top.

So I put here 4,5
5 would also be good
4 is doubtfull because it's not clear would it consider small peak after first big peak as a actuall peak or nor. If it go over that line it will be new peak (wich you want to avoid so take those safe numbers). In this example 4 is also OK, but 3,9 not. You can put some number and if not satisified change it so you get matching peak's max with your desired number that you can see from graph.

Now when we have tose interception points I've create ranges for MIN and MAX.

You will see blocks of data: first block 11111111...11, second 222222222...222, third 33333333333333...3333 etc

This is automatic and in different cases you'll get different number of blocks. If you have more data you'll need to extend range in formula (I put 10000). Also you can move more to the right (I put up to X column).

I calculate average for each block.
This average will change as you choose your number in B1 but not much.
MIN and MAX won't change - only I don't know how to get them

max_min(1).xls

Also notice that you have one min more than max. This is because you didn't consider start of curve (start from 0) as a first min.
I think this should be considered too so actually you have 10 MAX's and 11 MIN's, I put in formula 10 max, but can be changed according to this.

18. Re: Multiple peak values (max/min)

very old post but it has seriously helped me with my dissertation. Donkeyote you are my hero

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1