+ Reply to Thread
Results 1 to 5 of 5

Finding peak values above a certain threshold

  1. #1
    Registered User
    Join Date
    07-13-2019
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    3

    Finding peak values above a certain threshold

    Hi, I currently have to deal with analysing street noise.
    I have a table where sound level is recorded over time, i.e. I get two values per row, a timestamp and a decimal value between 35 an 100(ish).
    Now I want to identify the peaks (have their timestamp), if their duration is longer than 2 seconds and their value above 70.

    File example (not matching with the picture) looks like
    13-07-2019,00:16:59, 38.50
    13-07-2019,00:17:00, 78.30
    13-07-2019,00:17:01, 78.30
    13-07-2019,00:17:02, 38.90
    13-07-2019,00:17:03, 55.30
    13-07-2019,00:17:04, 39.90

    Maybe the picture makes it clearer
    sound.jpg

    Currently I have slightly over 200.000 lines only, but increasing by 86k/day, so at the end of my 10 days I'll end up with 864k lines

    Is there a chance to do this check by EXCEL or shall I go SQL ?
    Having a first solution with EXCEL would be a good start

    Txs

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Finding peak values above a certain threshold

    Please attach a sample Excel sheet (max 30 rows) with some manually calculated results to show us what you expect to see.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    07-13-2019
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    3

    Re: Finding peak values above a certain threshold

    Txs for having a look,
    here is an example sheet
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Finding peak values above a certain threshold

    I am far from an expert in this kind of signal processing, I don't know of too many shortcuts to doing this sort of thing. I find that I usually end up using a series of helper columns to A) Detect start and end of peak/trough, B) Identify max/min for each peak/trough, and C) anything else that I need to do to the signal.

    In your small sample set, the hardest part seems to be peak/trough detection. Here's how I did it:

    1) I often find that I like to use the SIGN() function for some steps, so the first thing is to subtract the threshold from the signal level. =C2-7000. Then feed that into the SIGN() function. Starting in G2, I entered =SIGN(C2-7000) and copied down to the bottom. This gives me a column of 1/-1 (or 0, if the level were ever exactly 7000).
    2) Detecting start/end of peak is a simple matter of detecting where this column changes sign. I put =IF(G2<0,0,1) into H2, then try something like =IF(PRODUCT(G2:G3)<0,H2+1,H2) into H3 and copy down. This gives me a column of increasing numbers, where a given number corresponds to an individual peak/trough. Peaks will be the odd numbers, while troughs will correspond to even numbers.
    3) Assuming that step 2 did its job correctly, determining the max for a given peak is a simple MAXIFS() function =MAXIFS($C$2:$C$28,$H$2:$H$28,peak number). I built a small table off to the side for this:
    3a) In K1:K4, I entered the odd integers 1,3,5,7.
    3b) In L1, I entered =MAXIFS($C$2:$C$28,$H$2:$H$28,$K1) and copied down.
    4) Once I have the max for each peak, getting the date/time stamp is a simple lookup function. =INDEX(A$2:A$28,MATCH($L1,$C$2:$C$28,0)) into M1. Copy M1 and paste into M1:N4.

    Is that helpful?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    07-13-2019
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    3

    Re: Finding peak values above a certain threshold

    Thanks MrShorty , I'll give it a try

+ 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. Replies: 5
    Last Post: 02-24-2019, 12:14 PM
  2. Replies: 12
    Last Post: 06-14-2017, 10:35 AM
  3. 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
  4. [SOLVED] Finding peak days of the within a period
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-24-2016, 04:18 PM
  5. Finding Peak and Trough
    By VJR in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-01-2014, 11:01 AM
  6. 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
  7. Replies: 5
    Last Post: 05-18-2013, 11:59 AM

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