How to extract x/y pairs for minima & maxima below and above defined thresholds? (Excel 2007)

[rover8721]

Hi,

I'm working with a large data set with paired values. Simply, column A is the x value (time) paired with the neighboring y value (signal) in column B. I'm seeking to only retrieve the x-y pair (in columns G & H, respectively) when y >= a certain threshold for maxima and y <= for minima. I've been able to achieve this with the code shown below (thanks to other similar situations solved by forum users!). The complication I'm running into now is where there are multiple data points for a single peak. Ideally, I'd like to report the average x and y values as a single entry for a situation like this, but even selecting only the pair with the greatest Y magnitude could be acceptable if the other route isn't viable. The most logical approach seems to be only extracting an x-y pair when its magnitude is greater than the data points on either side, but I haven't had any luck writing up such an equation. I've included a table below of a simplified situation, including the equations I've come up with so far and what I'd ideally like the result to look like. Also, it's important to me that there not be any spaces in the output data because of the next manipulations I'll be applying, but it would otherwise be manageable.

I'm fairly new to writing complex equations and the like in Excel, but any guidance would be greatly appreciated!!! Thank you all for reading this!


Sample table where A/B are raw data, D/E are the thresholds for maxima and minima, G/H are the retrieved pairs from the equations listed below, and J/K are the result I'd like to achieve.

	A	B	C	D	E	F	G	H	I	J	K
1	Time	Signal		Max	Min		Time	Signal		Time	Signal
2	0.1	0		0.04	-0.025		0.3	0.2		0.3	0.2
3	0.2	0					0.8	0.1		0.9	0.2
4	0.3	0.2					0.9	0.35		1.4	-0.1
5	0.4	0					1	0.15		1.75	-0.25
6	0.5	0					1.4	-0.1		2.4	0.3
7	0.6	0					1.7	-0.2		0	0
8	0.7	0					1.8	-0.3		0	0
9	0.8	0.1					2.4	0.3		0	0
10	0.9	0.35					0	0		0	0
11	1	0.15					0	0		0	0
12	1.1	0					0	0		0	0
13	1.2	0					0	0		0	0
14	1.3	0					0	0		0	0
15	1.4	-0.1					0	0		0	0
16	1.5	0					0	0		0	0
17	1.6	0					0	0		0	0
18	1.7	-0.2					0	0		0	0
19	1.8	-0.3					0	0		0	0
20	1.9	0					0	0		0	0
21	2	0					0	0		0	0
22	2.1	0					0	0		0	0
23	2.2	0					0	0		0	0
24	2.3	0					0	0		0	0
25	2.4	0.3					0	0		0	0
26	2.5	0					0	0		0	0

<tbody>
</tbody>

G2 equation

=IFERROR(
 INDEX(A$2:A$2402,
  SMALL(
   IF(B$2:B$2402>=D$2, ROW(A$2:A$2402)-ROW(A$2)+1,
    IF(B$2:B$2402<=E$2, ROW(A$2:A$2402)-ROW(A$2)+1
    )
   ),
  ROWS(A$2:A2)
  )
 ),
0)

H2 equation

=IFERROR(
 INDEX(B$2:B$2402,
  SMALL(
   IF(B$2:B$2402>=D$2, ROW(B$2:B$2402)-ROW(B$2)+1,
    IF(B$2:B$2402<=E$2, ROW(B$2:B$2402)-ROW(B$2)+1
    )
   ),
  ROWS(B$2:B2)
  )
 ),
0)