Identify peaks in a data set, select its previous values and get a chart

[alessandr0]

Hi there,
I have a set of data with approximatively 25500 cells, all contained in one column.
The data has a series of peaks that occurs, and I need to identify the 2nd peak (and the 4th, the 6th, and so on where present).
I have than to consider the previous 200 data points of the 2nd peak (corresponding to the previous 20 seconds) in order to get a data range for a chart.
When the values are identified, I have to store it in another column and take it as reference for the chart.
At present I do it manually, looking on the chart and using the Ms Office tool “find and search”…
Here linked the example file: https://drive.google.com/open?id=0B2...3huRzlEelB4cXM
I hope that it will possible to find a function to do automatically but I don't know how to do that in excel.
Thanks for the attention and your help
Alessandro

[CAntosh]

If I'm understanding your request correctly, I would think you can get the results you want using INDIRECT and ROW(). The formula below, placed in Row 1 of a new column and filled down, would return the max of each set of 200 rows:

=MAX(INDIRECT("B"&(200*ROW())-198):INDIRECT("B"&(200*ROW())+1))

[alessandr0]

Hi Cantosh and thanks for your reply,
is not the max of each set of 200 rows that I need;

Firstly it is necessary to identify the 2nd peak; to do that, I think that the following relation could be helpful as the data has the following shape:
start values are around 1E-10 (0,0000000001) than go up to 1E-06 (0,000001) -giving the first peak- after the values go down to 1E-08 (0,00000001) and then up again till 1E-06 (0,000001).
I need to find the highest value of the 2nd peak and, from where it is located, I have to select 200 cells back in order to get the chart range.

Did you see the file that I linked?

Thanks a lot

[CAntosh]

Ah, I misunderstood, thank you for the clarification. Can you try to provide some insight into how you define a 'peak'? There are some areas where the data distinctly crests (e.g. row 3711), but there are also multiple areas where the data goes up a little before going back down. How do you distinguish between meaningful peaks and these lesser mini-peaks?

[MrShorty]

I know that signal processing like this can occupy entire courses and textbooks, so there can be a lot that goes into signal processing like this. I am not an expert in signal processing, but occasionally I need to process signal like this. Strategies and principles that I use:

1) Your data look pretty smooth, so it may not be necessary to smooth the data, but I will usually look at the signal and see how noisy it is.
2) There's usually a baseline (with noise), and I need to identify a "threshold" value that identifies the regions that are interesting, and ignore the regions where it is just tracking baseline. This might be a column with a formula like =signal>threshold. A TRUE result from this function identifies the interesting regions. I usually hand enter the threshold, rather than try to program an algorithm into the computer to identify baseline and threshold.
3) For peak and trough recognition, I usually use a principle from calculus. Peaks and troughs occur where the "slope" or derivative of the curve changes sign. So I will usually have a column that computes slope (y2-y1)/(x2-x1) for the interesting regions, then additional columns to identify where the slope changes sign. (=IF(slope changes sign,previous row +1,previous row)) The details in this function will depend on how smooth the data are, which is why step 1 is so important.
4) With peaks and troughs marked, then I can use lookup functions to extract the needed information from the peaks and troughs, or filters to highlight the rows I need to see and so on.

As you can see, signal processing can get fairly involved. Is there a specific part of processing this signal that you need help with, or do you need the whole thing?

[CAntosh]

MrShorty's right in that there are probably more precise ways to approach this, but here's what I have based on the things I know how to do:

For working purposes, I'm considering 'peaks' with the following rules: 1) higher than the previous value (eliminates ties); 2) max of the previous and ensuing 200 values; 3) 100 times greater than the value 200 rows earlier. The conditional formatting formula applied from B201 to the end of the data looks like this:

=AND($B201=MAX($B2:$B401),$B201>$B200,$B201>100*$B2)

To capture that in text, I entered the formula below in N201 and filled down:

=IFERROR(IF(AND($B201=MAX($B2:$B401),$B201>$B200,$B201>100*$B2),"Peak",""),"")

Which will textually identify peaks. To add the preceding 200 rows, I entered the following in O201 and filled down:

=IF(COUNTIF($N201:$N401,"Peak")>0,$B201,"")

From there, you can just filter column O to remove the blank rows and you'll have your data set. If you hate filters, you can enter the following as an array (confirm w/ ctrl + shift + enter) formula in P1 and fill down to P1206 (in this case):

=IFERROR(INDEX($O$2:$O$25569, SMALL(IF(FREQUENCY(IF($O$2:$O$25569<>"", MATCH(ROW($O$2:$O$25569), ROW($O$2:$O$25569)), ""), MATCH(ROW($O$2:$O$25569), ROW($O$2:$O$25569)))>0, MATCH(ROW($O$2:$O$25569), ROW($O$2:$O$25569)), ""), ROW(A1)), COLUMN(A1)), "")

This leaves you with the 1206 entries from column B (now in column P) that consist of the peaks in rows 3711, 7314, 13789, 17394, 22291, and 25068 as well as the 200 rows preceding each peak. Hopefully this is close to the mark?

[José Augusto]

Hi
I used helper Columns C, to H to get de peaks
Formulas I use:
C2:C12785

Please Login or Register  to view this content.

D2:H12785

Please Login or Register  to view this content.

C1:H1

Please Login or Register  to view this content.

L1:Q1

Please Login or Register  to view this content.

L4:Q4

Please Login or Register  to view this content.

L5:Q5

Please Login or Register  to view this content.

L6:Q6

Please Login or Register  to view this content.

U2 You must adjust this value (Amplitude) to get accuracy
V2 You can adjust this value (Zero)
D_Times $A$2:$A$12785
D_Flows $B$2:$B$12786
See the file find peak and chart(2).xlsb. I delete half lines in your data to reduce file size (not important) and binary save

[alessandr0]

First of all, thank you to everyone for the support;

@MrShorty "Is there a specific part of processing this signal that you need help with, or do you need the whole thing?"
I do not need the whole thing, the part of interest is the following as shown in the picture here above:
grafico finale.jpg

@cantosh, I am sorry but I am not able to understand the steps you have done and I tried to repeat in the excel file but I am doing surely some errors; could you please attach the file?

@José Augusto, it is impressive what you provided and helpful; could you explain how to modify amplitude and "zero"? The references are not really exacts (I imagine that is due to the accuracy);
For example in M1 the value has to be 3659 instead of 3634 and the range in M4 B3459:BB3659. Could you please check it?

Thanks ++

[CAntosh]

The attachment shows where I ended up. Column P is your data: the 6 peaks as defined above and the 200 preceding entries for each. Columns N and O are helper columns. The calculation to get column P can be a bit slow - I would advise against filling all the way down through column P, since you only need the first 1206 rows. Ultimately, I would recommend just filtering the blanks out of column O, but that's up to you.

Note that what I've done will fail to catch any peak in the first 200 rows, but this didn't seem to be an issue with your data.

Hope it helps!

[José Augusto]

Hi Alessandro
I have some minor inconsistencies in some formulas. I correct these errors and insert new formulas.
To answer your questions see the file find peak and chart(3) (1).xlsb