+ Reply to Thread
Results 1 to 7 of 7

Finding Peak and Trough

  1. #1
    Registered User
    Join Date
    04-30-2014
    Location
    London,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Finding Peak and Trough

    Hello all,
    I need to find the Peak and Trough for all the points in the attached graph and the data point used for plotting it. How do i find it. (Beginner pls excuse)
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Finding Peak and Trough

    When you say peak and trough, do you just mean the co-ordinates of points on the graph? I'd use the following formula in the two rows next to the data, first just insert a row at the top and make cell A1 = 0:

    In col B:

    =AND(A2>A1,A2>A3)

    In col C:
    =AND(A2>A1,A2>A3)

    THen just drag down. 'TRUE' in col B means it's found a peak, and 'TRUE' in C means a trough.

    Let me know if it works!

  3. #3
    Registered User
    Join Date
    04-30-2014
    Location
    London,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Finding Peak and Trough

    Thanks for the suggestion , But am getting only one peak value but I would need to find out all the Peak and Trough and as shown in graph i have attached and i need to find the average of the peak and trough to get a polynomial curve... and the result from it...

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Finding Peak and Trough

    OK, so how you plan to "get a polynomial curve"?
    I prepared rough scatterplot and it does not look like really polynominal for me, but may be I'm wrong.
    Anyway after you have the curve fitted then you "just need" local extrema (minima and maxima) - but having the fitted curve equation known it is usually relatively easy task.
    Best Regards,

    Kaper

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Finding Peak and Trough

    Looks like a harmonic function, probably some kind of step-like instead of a sinewave; there's drift in the amplitude and frequency over time (looks like amplitude is increasing and frequency is decreasing), as well as a trend downards that may be linear but I guess might be logrithmic. Well, it might have some kind of second or third order behavior as a sinusoid rather than actually being a modified square wave.

    Anyway, I doubt it's exactly a polynomial.

    I feel like figuring out what kind of equation is governing this behavior is too fuzzy for a computer. Once you've got the formulation you can drive the coefficients down with least squares + solver, but the human needs to figure out what's going on here.

  6. #6
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Finding Peak and Trough

    The above suggestion should return all local peaks and troughs.

  7. #7
    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: Finding Peak and Trough

    I think you need to apply a large binomial or Gaussian filter to the data to smooth it before trying to locate the extrema.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding multiple peak values and reporting associated data
    By jsmith6 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 07:16 PM
  2. Replies: 5
    Last Post: 05-18-2013, 11:59 AM
  3. [SOLVED] Finding trough points in a data series
    By Sie in forum Excel General
    Replies: 8
    Last Post: 05-27-2012, 04:34 PM
  4. Finding peak usage times
    By afitzwater in forum Excel General
    Replies: 2
    Last Post: 01-04-2012, 05:19 PM
  5. Formula Finding Peak Period in Time Log
    By wrongrobot in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2007, 08:51 PM

Tags for this Thread

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