Peak and Valley points of multiple stepped curve in Excel graph

**venkatm** · 07-31-2012, 05:53 PM

Hi All,

Please see attached picture of the test output graph. I have got around 8000 data points for X and Y as output from the test machine. I need to find the points for Peak and Valley of these (sudden drop) steps. I intend to calculate the Delta Y values for these steps. It is okay to use either the graphical method or the data points from the x and y column.

Please suggest a solution. I also want to know the possiblity of deleoping a macro so that a user can copy paste the two columns and program automatically calculates the sum of Delta Y of the steps.

Regards,
Venkat

**shg** · 07-31-2012, 06:00 PM

How could someone suggest or test a solution on a picture?

**venkatm** · 07-31-2012, 06:16 PM

@shg,

Thanks for asking that question. Please see the attached test output excel sheet. I have plotted a graph using these data points. I need to find the Delta Y values for the steps. Let me know if you need more clarification.

Regards,
Venkat

**shg** · 07-31-2012, 06:44 PM

Do you really mean find the points where the slope ascends and descends through -45 degrees, or the points where the second derivative changes signs, or something else?

**venkatm** · 07-31-2012, 06:57 PM

Thank you shg. If you see the stepcurve.jpg, there are four steps where the X values stays almost constant and Y values increase rapidly. I need to find the beginning and end of this phenomina. Delta Y for all these four steps needed.

Regards,
Venkat

**shg** · 07-31-2012, 08:19 PM

Then the points of interest are where the maximum (negative) slopes ocur?

**MrShorty** · 07-31-2012, 08:25 PM

I'm not yet sure exactly how I'd do it, but my first thought was like shg's - use the first and maybe second derivatives to locate the "steps". The devil is in the details, but the basic idea might look like:

1) calculate 1st and 2nd derivative
2) steps begin/end where the spikes are in the 2nd derivative.
3) Having located the spikes in the 2nd derivative, delta y is simply y2-y1. the hardest part here is returning the y's at the corresponding spikes in the 2nd derivative.

**venkatm** · 08-01-2012, 06:18 PM

Hi shg and MrShorty

Thank you for your ideas. Based on your suggestion I have calculated the 1st and 2nd derivative.Also plotted a Graph with Second derivative in Y-axis and input revolutions values in X axis. I am not sure how to find the corresponding (X,Y) values for the spikes in the second derivative graph. How do I do that?

Regards,
Venkat

**shg** · 08-01-2012, 07:24 PM

Rather than comment on that, may I ask ....

Overall, the line slopes downward at about 45 degrees (slope ~ =0.98)

If it were rotated to horizontal, would the points of interest be the minima and maxima?

Or the same thing, stated another way: Would the points of interest be those that are the maximum distance from a linear trendline (which you could add to see)?

**venkatm** · 08-01-2012, 08:01 PM

I like the concept but the Delta Y will not be the same if we rotate the graph.

I like to go by the Graphical approach. If there is a way to make the Grids (X and Y axis equal scale) square in Excel then I can plot this graph and print it. Then the peak points can be traced approximately.

**shg** · 08-01-2012, 08:11 PM

Assume for the moment that the correct delta Y can be calculated. My question is, are those the points of interest?

**venkatm** · 08-01-2012, 08:20 PM

Yes. Those are the points of interest...

**shg** · 08-01-2012, 08:35 PM

Take a look at the attachment. It's visually clear where the points of interest are in sample2, not so much in sample1. Those two graphs would be a good choice for annotating and pasting as a pictures, along with some supporting rationale ...