+ Reply to Thread
Results 1 to 18 of 18

Multiple peak values (max/min)

  1. #1
    Registered User
    Join Date
    03-26-2009
    Location
    Noruega
    MS-Off Ver
    Excel 2003
    Posts
    4

    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.
    Attached Images Attached Images
    Last edited by heffalompen; 09-02-2009 at 08:51 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Multiple peak values (max/min)

    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.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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:

    Please Login or Register  to view this content.
    You can then simply obtain your result using:

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Multiple peak values (max/min)

    But there are some peaks that are excluded

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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 % fluctuation etc...) ... sorry I missed that point.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    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. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Multiple peak values (max/min)

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

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Multiple peak values (max/min)

    Edit: removed formula as it addressed "peak" values in the data but not for the chart peaks.
    Last edited by Palmetto; 09-02-2009 at 09:18 AM.

  9. #9
    Registered User
    Join Date
    03-26-2009
    Location
    Noruega
    MS-Off Ver
    Excel 2003
    Posts
    4

    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.
    Attached Files Attached Files

  10. #10
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    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. #11
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Multiple peak values (max/min)

    Maybe a bit simplistic, but a possible approach.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-26-2009
    Location
    Noruega
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Multiple peak values (max/min)

    Quote Originally Posted by WHER View Post
    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.
    Last edited by heffalompen; 09-02-2009 at 11:10 AM.

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    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. #14
    Registered User
    Join Date
    03-26-2009
    Location
    Noruega
    MS-Off Ver
    Excel 2003
    Posts
    4

    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)
    Attached Images Attached Images
    Last edited by heffalompen; 09-02-2009 at 05:11 PM.

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

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

    Then adjust C1.
    Last edited by shg; 09-02-2009 at 06:18 PM.
    Entia non sunt multiplicanda sine necessitate

  16. #16
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    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. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    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.
    Last edited by zbor; 09-03-2009 at 01:29 AM.

  18. #18
    Registered User
    Join Date
    11-25-2013
    Location
    belfast
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Multiple peak values (max/min)

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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