+ Reply to Thread
Results 1 to 13 of 13

Interesting problem finding peak to peak values in a dataset

  1. #1
    Registered User
    Join Date
    06-08-2017
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    5

    Interesting problem finding peak to peak values in a dataset

    Hi,
    I have a waveform which contains a series of five samples.
    original.PNG
    I need to get the max peak-to peak value from each of the samples.
    original.PNG

    So at the moment my idea is to filter the flat lines between each sample to break them up, then scan through each individually.
    I' managed to filter the flat lines though a comparison:
    C3=ABS(B4-B3) etc
    then
    =IF(AND(C3<0.01,C4<0.01),NA(),B3) etc
    filtered.PNG

    but I'm stuck as to how I can determine the peak to peak of what remains.

    Any ideas guys?
    Attached Images Attached Images

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Interesting problem finding peak to peak values in a dataset

    Interesting puzzle, but hard to solve without the data.
    Can you upload the workbook with the data?
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: Interesting problem finding peak to peak values in a dataset

    and while you're at it, please amend your profile to show which version of Excel you are using and your geographical location. Excel's regional settings and version capabilities may hugely affect the answers that are needed to solve your problem. Your current entries tell us NOTHING USEFUL.
    Glenn



  4. #4
    Registered User
    Join Date
    06-08-2017
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Interesting problem finding peak to peak values in a dataset

    Thanks for the reply.
    I've attached the file as a zip and updated location.
    London, UK and using Excel 2013

    EDIT: Won't let me upload a zip or XLSB for some reason.
    Link here to XLSX instead: https://ufile.io/ynzob
    Last edited by cianmartin; 06-09-2017 at 05:37 AM.

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: Interesting problem finding peak to peak values in a dataset

    I am currently on a slow connection Please reduce the size of your file to < 1 Mb (delete XS data - jusy show us 2 of your waveforms, not 5) and repost HERE, not on that DEADLY slow file sharing site.

  6. #6
    Registered User
    Join Date
    06-08-2017
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Interesting problem finding peak to peak values in a dataset

    Here you go. Two columns. There's about half the data set here, but it should be enough to work with.
    Thank you very much!
    Attached Files Attached Files

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: Interesting problem finding peak to peak values in a dataset

    Lawdie!! What a lot of data...

    Normally with this sort of thing, the biggest problem is separating true maxima from adjacent false maxima. In thsi case there are just so many data points, I've taken the "hairy man's approach".

    I plotted the data and (so far, anyhow) manually assigned x-axis bands to cover each peak. Within each band I used an array formula to determine the max/min values.

    =MAX(IF($A$2:$A$50000>$E3,IF($A$2:$A$50000<$F3,$B$2:$B$50000)))

    and similarly for MIN

    =MIN(IF($A$2:$A$50000>$E3,IF($A$2:$A$50000<$F3,$B$2:$B$50000)))

    or, more neatly combined (columns G&H can be deleted):
    =MAX(IF($A$2:$A$50000>$E3,IF($A$2:$A$50000<$F3,$B$2:$B$50000)))-MIN(IF($A$2:$A$50000>$E3,IF($A$2:$A$50000<$F3,$B$2:$B$50000)))

    finally, a simple formula to determine which out of each "pair" of data exhibited the greated peak:peak range:

    =IF(D4=D3,MAX(I3:I4),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    Is this good enough, or do you need some way of automatically assigning the ranges for the max/min formulae?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-08-2017
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Interesting problem finding peak to peak values in a dataset

    Wow, that's great!

    Instead of manually setting limits, my plan was to zero out the dead time between each sample, then find each sample with something like "find where 100 samples are not zero" to get the start of the series, offset from that point and run "find where 100 samples are zero" to get the end of the sample. Then run something like your algorithm to get max and min points.

    The example I posted is quite an easy sample, where the max and min points in each sample are at roughly the same time, so you assume they belong to the same peak to peak measurement. However, there may be cases where the max and min point don't correspond, like in the crudely drawn example below. That's when things get complicated and I wonder if excel is even the best tool for this job.
    example.png

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: Interesting problem finding peak to peak values in a dataset

    That bit's easy... if I've picked you up correctly. Column I.

    incidentally, with so many data points, OFFSET might slow things up soooo much.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-08-2017
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Interesting problem finding peak to peak values in a dataset

    Yep, I see what you're doing. It's automated the bulk of the process, though it still involves splitting the sample and manually identifying the peaks.
    Easy enough when there's two clear peaks per sample here, but more difficult if there are a dozen similar peaks, especially if the max and min points are mismatched.

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,593

    Re: Interesting problem finding peak to peak values in a dataset

    Post a sample of some "dirtier" data. However, I'm beginning to run aground...

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,258

    Re: Interesting problem finding peak to peak values in a dataset

    Hi,

    I'd do an advanced filter on your data using an "OR" to only select data above a few standard deviations and below the same. See the attached to see how it comes out. This might give you an easier problem. Note - after doing the Advanced filter I deleted the long columns to make it fit.

    Adv Filter of Signal GK2.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Interesting problem finding peak to peak values in a dataset

    Man, this is wrecking my brains
    Thought I found a method to isolate the samples (that is the main point, rest is easy), but it's not working.

    Are durations of the samples or the pauses between them (almost) equal?

+ 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. Large function: finding the values of the 3rd and 4th peak with duplicates
    By fionafiona in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-16-2017, 08:16 AM
  2. Replies: 6
    Last Post: 05-01-2015, 12:02 PM
  3. Electricity Calculations based on Peak and Off Peak Rate
    By mrwrighty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 07:43 AM
  4. Counting the peak & non peak hours between two dates?
    By zeroprobe in forum Excel General
    Replies: 3
    Last Post: 12-09-2013, 06:53 AM
  5. Replies: 5
    Last Post: 05-18-2013, 11:59 AM
  6. Formulas - converting dBdsx to Volts peak to peak
    By alex148 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2007, 09:49 AM
  7. Replies: 1
    Last Post: 03-13-2006, 07:27 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