Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    picking low/high point from data (2003)

    <big>Edited by HansV to reduce picture in size - PLEASE don't post pictures larger than 640 x 480 pixels - yours was 1443 x 1380 <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>.</big>

    I have instrumentation data sets that show cycles as indicated on the attached graph. I would like to average the data, but I want to make sure that I am averaging complete cycles; ie. from low point to low point, and I'm not averaging from a low point to a high point. The data are in elapsed time in column A and meter reading in column B.

    any suggestions on how to pick the start and stop points for averaging? Something like setting low point flags in column C would work. However, looking at the meter data to see when the data stops decreasing and starts increasing will not work as there is often increasing values between low points as shown on the 2nd and 3rd cycles on the graph.

    Thanks!
    Attached Images Attached Images
    • File Type: jpg x.jpg (8.0 KB, 0 views)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: picking low/high point from data (2003)

    One way to massage the data to remove some of the smaller variations is to at a point plot the median of it and the point before and after or even the 2 pts before through the 2 poins after and then test the change on these points. There are other techniques for smoothing of data, but many are not applicable since you seem to want the larger variations.

    Perhaps posting a sample file with data (instead of a picture) would allow people to play and see what they come up with...

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: picking low/high point from data (2003)

    thanks for the ideas
    data set attached
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: picking low/high point from data (2003)

    Here is what I came up with. The chart plots the old/new along with the average of each series

    Median did not work well as it did not smooth the data enough to get rid of the intermediate minimums.

    Starting with the 4th point and continuing until the 4th to the last point, I fit the point and the 3 points before and 3 points after (7 points) into a second order curve using LINEST. (eqn is Y= A0+ A1*x + A2*x^2) and calculated the slope/derivative of that fitted curve (dy/dx = A1+2* A2 * x). Where the derivative goes from negative to positive slope is a minimum. I found even with relatively extensive smoothing I still got the intermediate minimums so I also required not only going from negative to positive slope but that the sensor reading was <880 (value in J1)

    So Cols F/G has the tangents for old and New sensor. Cols H/I has the calc to determine "type". 0 is from start to first min. 1 from 1st to 2nd min etc (The last series, 7, is from the last min to the end and is, like the 0th series, not complete). From the values in H/I one can determine the row that starts the series type (using match) and with this row determine the time and sensor reading. M20:O26 has this for the old data and M30:O36 for the new sensor readings. P20:P26 has the average of the old sensor readings for each series and P30:P36 the new sensor readings.

    K40:L56 has the X/Y series pattern for the average lines and thus M40:N56 has the time and average points to draw the average lines for the old sensor and O4056 has teh time and averages for the new sensor lines.

    [Perhaps someone else will come up with a simpler smoothing algorithm that doesn't require the tangent or defining a set "Upper limit" to where it can be, but his was about the best I could do...]

    Steve
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: picking low/high point from data (2003)

    Steve:

    Thanks so much for your innovative expertise. The spreadsheet works great!
    Thanks.

    John

Posting Permissions

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