New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Finding the peaks and troughs for large samples of data

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Finding the peaks and troughs for large samples of data

Postby Rich721 » Tue Nov 15, 2016 11:29 am

Hi, please could someone offer some suggestions as to how to find the peaks and troughs when dealing with large samples of data?

Here is an image of the produced graph:

Image

I have searched and tried to use some of the ideas in other threads, but I have a few problems:

1. my data has 2500 points, the points have some fluctuations. The tip of the peaks shown in the image are composed of say 100 points which have some variation. Therefore finding the largest number will not work.

2. the peaks change in size. therefore I can't use LARGE and SMALL functions to find the highest and lowest points. i.e. the points leading up to a big peak are greater than the peak of the small peaks.

Please could someone point me in the right direction as to how to solve these two issues?

Any help would really be appreciated. I have attached the spreadsheet to show the data variation issue.
  • 0

You do not have the required permissions to view the files attached to this post.
Rich721
Rookie
 
Posts: 1
Joined: Nov 15, 2016
Reputation: 0
Excel Version: 2013

Re: Finding the peaks and troughs for large samples of data

Postby gebobs » Thu Nov 17, 2016 12:36 pm

See the attached sheet. I moved the chart to its own tab so I could take a closer look at it.

With such large sets of real data, replete with measurement noise of some sorts or another, discerning the true maxima and minima is not realistic. Rather, you can point to an area where these peaks and troughs occur and define them with approximate coordinates picked somewhat arbitrarily and with decreased precision (i.e. perhaps just two significant figures would be sufficient).

At any rate, what I did was calculate the slope of the line using a running calculation of 25 data points. The peaks and troughs will be at the transition where the slope goes from positive to negative (for a peak or maximum) or negative to positive (for a trough or a minimum). For clarity, I attenuated the slope by a factor of 10^6. If it was my data, I would do that for the time data and just define it as such so your chart wouldn't need to display scientific notation on the x-axis.

At any rate, you have eight maxima and seven minima. I added some conditional formatting to highlight where these transitions occur and added some arrows approximately at the point that they tip. There are also two areas between the last minima and maxima where the slope approaches zero but there is no transition. They are probably not significant in the grand scheme of your test, but interesting that the formatting picks it out. I added another chart (No transitions) to show where these are. They are just small plateaus.

Anyhoo...hope this helps. Your spreadsheet is in 97-2003 format. I hope what I did comes through clearly. If you need any more help, let me know.
  • 0

You do not have the required permissions to view the files attached to this post.
gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 61 guests