Detect peak maximum on a spectrum from the raw data

[mejohn]

Hello everyone!
I’m trying to detect a peak maximum from the raw data of a spectrum, but only if the peak rises and falls by a minimum amount.

The formula I currently have will detect this, but only on sharp peaks which rise and fall by the minimum amount between adjacent row values.
So, if B10-B9>=positive minimum value AND B11-B10<=negative minimum value, then the peak is detected.
But if the peak is shallow, then the difference between adjacent row values is smaller than the minimum, and the peak will not be detected.

Here’s the main formula on Column E
=IFERROR(IF(((B10-B9)/($A9-$A10))>0,IF(AND((B9-B8)/($A8-$A9)>0,(B10-B8>=0.0006),(B11-B10<=-0.0006)),"True",""),""),"")

Columns C and D are not used in the formula, they only inform if the difference between adjacent row values are positive (peak rise) or negative (peak fall). Column D shows this in 1's (rise) and 0's (fall).

I’d like to account for all the consecutive peak rises (positive values on Column C) after a peak fall (negative values on Column C), and determine if the difference between the start of the peak rise and it’s fall is >=0.0006
At the same time, account for all consecutive peak falls (negative values) after a peak rise, and determine if the difference between the start of the peak fall and the start of the next peak rise is <=-0.0006
If both of these conditions are met, then report "True", as a peak was detected.

I hope this makes sense. Thank you all in advance for your time!

 

[mejohn]

Hi Peter,

The new formula works perfectly!

Thank you so much for your help!

 

[Peter_SSs]

The new formula works perfectly!

I am having second thoughts.
So, just checking, should the yellow cell below show 'True'?

mejohn.xlsm
	A	B	C	D
1	Wavelength (nm)	A		Peak Detected
2	500	0.000056
3	499	0.000071	1
4	498	-0.000063	0
5	497	-0.000075	0
6	496	0.000071	1
7	495	-0.000063	0
8	494	-0.000075	0
9	493	-0.000682	0
10	492	-0.000017	1
11	491	-0.000737	0
12	490	-0.000736	1
Sheet7

Cell Formulas
Range	Formula
C3:C12	C3	=(C2+1)*(B3>B2)
D3:D12	D3	=IF(OR(C3=0,C4>0),"",IF(MIN(B3-XLOOKUP(1,C$2:C2,B$2:B2,0,0,-1),B3-XLOOKUP(1,C4:C$100,B3:B$99,B$99))>=0.0006,"True",""))

 

[mejohn]

Hi Peter,
That data seems to be a subset of the data I provided.
But using the data you presented, yes, 492 should be a peak maximum and reflected as "True"
B10-B9 = 0.000665 (which is >=0.0006)
B11-B10 = -0.00072 (which is <=-0.0006)

Good catch!

 

[Peter_SSs]

That data seems to be a subset of the data I provided.

Sort of - I fudged some of the numbers to produce that single "1" with surrounding values that I though might trigger a 'True'

However, before I consider a formula modification, it may be that the logic of the existing formula is not quite correct & I would like that checked.
When we think that we might have a peak, we have to check upwards to ensure >=0.0006 and we have to check downwards for <=-0.0006. Is that correct?

If so, when looking at row 39 in the sample below ..
When checking upwards, should I be checking the difference between yellow and blue or should I be checking between yellow and green to confirm >=0.0006?

Similarly, what about when checking downwards?

My existing formula is comparing blue upwards and green downwards which is probably not correct? Should it be green in both directions? .. or something else?

mejohn.xlsm
	A	B	C	D
25	477	0.00004	0
26	476	0.000052	1
27	475	0.000154	2
28	474	0.00024	3
29	473	0.000393	4
30	472	0.00057	5
31	471	0.000909	6
32	470	0.001472	7
33	469	0.002217	8
34	468	0.00356	9
35	467	0.00599	10
36	466	0.010754	11
37	465	0.017665	12
38	464	0.024182	13
39	463	0.026645	14	True
40	462	0.025708	0
41	461	0.024894	0
42	460	0.024955	1
Sheet7

Cell Formulas
Range	Formula
C25:C42	C25	=(C24+1)*(B25>B24)
D25:D42	D25	=IF(OR(C25=0,C26>0),"",IF(MIN(B25-XLOOKUP(1,C$2:C24,B$2:B24,0,0,-1),B25-XLOOKUP(1,C26:C$100,B25:B$99,B$99))>=0.0006,"True",""))

 

[mejohn]

Hi Peter,

Yes, "we have to check upwards to ensure >=0.0006 and we have to check downwards for <=-0.0006"

For row 39, the peak fall is calculated by subtracting B41-B39, so green minus yellow.
This is because after peak maximum at row 39, the peak falls from its maximum 39 to row 41. In this case, the total fall from row 39 to 41 (B41-B39) needs to be <=-0.0006.
Between row 41 and row 42, the peak (absorbance) starts going back up, so row 42 would not be included as a peak fall.
Also B42-B41 would be included in the calculation of a peak rise, if there was more data after row 42.

Thank you so much for being diligent and accurate with the calculations.

 

[Peter_SSs]

For row 39, the peak fall is calculated by subtracting B41-B39, so green minus yellow.

Thanks, but that is for the downwards check only. You didn't clarify for the upwards check.
Should it be B39-B26 (blue) or B39-B25 (green)?

 

[mejohn]

Hi Peter,
Sorry, I missed that.
The peak rise for peak maximum at wavelength 463 should be calculated by B39-B25, yellow-green.
Thank you!

 

[Peter_SSs]

The peak rise for peak maximum at wavelength 463 should be calculated by B39-B25, yellow-green.

Thanks. That means my previous formula was incorrect with its 'upwards' check. Try this instead.

mejohn.xlsm
	A	B	C	D
1	Wavelength (nm)	A		Peak Detected
2	500	0.000056
3	499	0.000071	1
4	498	-0.000063	0
5	497	-0.000075	0
6	496	0.000071	1
7	495	-0.000063	0
8	494	-0.000075	0
9	493	-0.000682	0
10	492	-0.000017	1	True
11	491	-0.000737	0
12	490	-0.000736	1
13	489	-0.000064	2
14	488	-0.000002	3
15	487	-0.000106	0
16	486	-0.00004	1
17	485	0.000065	2
18	484	-0.000018	0
19	483	0.000084	1
20	482	-0.000017	0
21	481	-0.000015	1
22	480	0	2
23	479	-0.000045	0
24	478	0.000051	1
25	477	0.00004	0
26	476	0.000052	1
27	475	0.000154	2
28	474	0.00024	3
29	473	0.000393	4
30	472	0.00057	5
31	471	0.000909	6
32	470	0.001472	7
33	469	0.002217	8
34	468	0.00356	9
35	467	0.00599	10
36	466	0.010754	11
37	465	0.017665	12
38	464	0.024182	13
39	463	0.026645	14	True
40	462	0.025708	0
41	461	0.024894	0
42	460	0.024955	1
43	459	0.025687	2
44	458	0.028208	3
45	457	0.031899	4
46	456	0.03434	5
47	455	0.034612	6	True
48	454	0.033498	0
49	453	0.031312	0
76	426	-0.004466	0
77	425	-0.004258	1
78	424	-0.003262	2
79	423	-0.00098	3
80	422	0.003508	4
81	421	0.011929	5
82	420	0.026353	6
83	419	0.049481	7
84	418	0.084763	8
85	417	0.135364	9
86	416	0.203233	10
87	415	0.288077	11
88	414	0.384543	12
89	413	0.488092	13
90	412	0.601672	14
91	411	0.729055	15
92	410	0.861495	16
93	409	0.977181	17
94	408	1.059937	18
95	407	1.118963	19
96	406	1.165861	20
97	405	1.200559	21
98	404	1.214909	22	True
99	403	1.213558	0
Sheet8

Cell Formulas
Range	Formula
C3:C49,C76:C99	C3	=(C2+1)*(B3>B2)
D3:D49,D76:D99	D3	=IF(C3>C4,IF(MIN(B3-XLOOKUP(1,C$3:C3,B$2:B2,0,0,-1),B3-XLOOKUP(1,C4:C$100,B3:B$99,B$99))>=0.0006,"True",""),"")

 

[mejohn]

Hi Peter,

I made manual modifications to the data to better test the formula and found that the latest or previous formulas fail to detect a peak at row 352 (wavelength 457) in the data below.

Thanks!

Appendix B_Template Methanol.xlsx
	A	B	C	D								L
345	464	-0.000166	0
346	463	-0.000137	1
347	462	-0.00015	0
348	461	-0.000149	1
349	460	-0.000141	2
350	459	-0.00014	3
351	458	-0.000101	4
352	457	0.000455	5									0.00061
353	456	-0.000086	0									-0.00060
354	455	-0.000096	0
355	454	-0.0001	0
356	453	-0.000103	0
357	452	-0.00014	0
358	451	-0.000144	0
359	450	-0.000117	1
360	449	-0.000111	2
Raw Data Combined

Cell Formulas
Range	Formula
C345:C360	C345	=(C344+1)*(B345>B344)
D345:D360	D345	=IF(C345>C346,IF(MIN(B345-XLOOKUP(1,C$10:C345,B$9:B344,0,0,-1),B345-XLOOKUP(1,C346:C$600,B345:B$599,B$599))>=0.0006,"True",""),"")
L352	L352	=B352-B347
L353	L353	=B358-B352

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:D599	Cell Value	contains "True"	text	NO

 

[Peter_SSs]

I don't believe that row 352 is a peak. Your values in column L (as shown) are incorrect. If you physically look at B358, the 6th decimal place is a 4. For B352 the 6th decimal place is a 5. So subtracting them can not leave a zero in the 6th decimal place as "appears" (or does not appear) in cell L353. I suspect that you have column L set to display 5 decimal places only?

If you manually do that subtraction, the result is -0.000599 which is not <=-0.0006 hence no peak.
(L352 is also different. The actual value is 0.000605 but is this case it doesn't 'flip' the result to the other side of >=0.0006 so no harm done)

Having said all that, I cannot guarantee that a peak will not be missed, or an extra peak could get included due to the fact that calculating in Excel is not always perfect.
See Floating-point arithmetic may give inaccurate result in Excel - Office