1 Attachment(s)

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

Attachment 632212

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

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.

1 Attachment(s)

Re: Finding peak values above a certain threshold

Txs for having a look,

here is an example sheet

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?

Re: Finding peak values above a certain threshold

Thanks MrShorty :), I'll give it a try