finding changes and looking back

[dmheller]

i have a set of data, starts in column A and ges to P then i was messing with it in Q and R but in column D, the values will be low then spike up to ~15. What i want to do is find that spike it will happen i believe 15 times, and look back about 10 seconds then take an average of the last 10 data points. So the first spike is at 8:28:53, i want to go 10 rows up to 8:28:43 then average 8:28:33 to 8:28:43. Then i would need to do this all again at the 8:30:01 time stamp. Back 10 seconds, average back.
Any help would be great.
These would go into a new column labeled 1-14.
Thanks in advance.

testo 7-26-24.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
30	7/26/2024 8:28:30 AM	28	74.3	0.25	2466	11.37	1033.0	1033	0.0	154	89.3	1.2	10.7	0.97	63.1	x5	0.29	0.286875
31	7/26/2024 8:28:31 AM	29	74.3	0.21	2351	11.40	1031.0	1031	0.0	154	89.3	1.0	10.7	0.97	63.1	x5	0.29	0.2875
32	7/26/2024 8:28:32 AM	30	74.3	0.16	2236	11.44	1029.0	1029	0.0	155	89.4	0.8	10.6	0.97	63.1	x5	0.29	0.285
33	7/26/2024 8:28:33 AM	31	74.3	0.16	2126	11.44	1027.0	1027	0.0	157	89.4	0.8	10.6	0.97	63.1	x5	0.28	0.2825
34	7/26/2024 8:28:34 AM	32	74.3	0.16	2033	11.45	1025.0	1025	0.0	159	89.4	0.8	10.6	0.97	63.1	x5	0.28	0.280625
35	7/26/2024 8:28:35 AM	33	74.3	0.20	1967	11.44	1024.0	1024	0.0	161	89.4	0.9	10.6	0.97	63.1	x5	0.28	0.281875
36	7/26/2024 8:28:36 AM	34	74.3	0.23	1934	11.42	1024.0	1024	0.0	163	89.5	1.1	10.5	0.97	63.3	x5	0.28	0.283125
37	7/26/2024 8:28:37 AM	35	74.3	0.22	1939	11.42	1024.0	1024	0.0	164	89.5	1.1	10.5	0.97	63.3	x5	0.28	0.279375
38	7/26/2024 8:28:38 AM	36	74.1	0.22	1984	11.42	1025.0	1025	0.0	165	89.4	1.1	10.6	0.97	63.3	x5	0.27	0.270625
39	7/26/2024 8:28:39 AM	37	74.1	0.19	2061	11.43	1025.0	1025	0.0	167	89.4	0.9	10.6	0.97	63.3	x5	0.26	0.26
40	7/26/2024 8:28:40 AM	38	74.1	0.18	2157	11.43	1024.0	1024	0.0	168	89.4	0.9	10.6	0.97	63.3	x5	0.25	0.245625
41	7/26/2024 8:28:41 AM	39	74.1	0.20	2249	11.41	1024.0	1024	0.0	169	89.4	1.0	10.6	0.97	63.3	x5	0.23	0.233125
42	7/26/2024 8:28:42 AM	40	74.1	0.25	2319	11.38	1025.0	1025	0.0	170	89.4	1.2	10.6	0.96	63.3	x5	0.23	0.22625
43	7/26/2024 8:28:43 AM	41	74.1	0.35	2358	11.32	1027.0	1027	0.0	170	89.3	1.7	10.7	0.97	63.1	x5	0.23	0.22625
44	7/26/2024 8:28:44 AM	42	74.1	0.35	2376	11.32	1030.0	1030	0.0	170	89.3	1.7	10.7	0.97	63.1	x5	0.23	0.226875
45	7/26/2024 8:28:45 AM	43	74.1	0.36	2394	11.31	1032.0	1032	0.0	171	89.3	1.7	10.7	0.97	63.0	x5	0.23	0.230625
46	7/26/2024 8:28:46 AM	44	74.1	0.37	2406	11.31	1032.0	1032	0.0	171	89.3	1.8	10.7	0.97	63.0	x5	0.24	0.238125
47	7/26/2024 8:28:47 AM	45	74.1	0.38	2409	11.30	1032.0	1032	0.0	172	89.3	1.8	10.7	0.97	63.1	x5	0.25	0.24875
48	7/26/2024 8:28:48 AM	46	74.1	0.42	2389	11.28	1031.0	1031	0.0	173	89.3	2.0	10.7	0.97	63.0	x5	0.27	0.265
49	7/26/2024 8:28:49 AM	47	74.1	1.39	2343	10.74	1027.0	1027	0.0	172	89.3	7.1	10.7	0.97	63.0	x5	0.34	0.341875
50	7/26/2024 8:28:50 AM	48	73.8	4.86	2266	8.80	1007.0	1007	0.0	175	89.1	30.3	10.9	0.97	63.0	x5	0.64	0.635625
51	7/26/2024 8:28:51 AM	49	73.8	9.51	2168	6.21	960.0	960	0.0	199	88.8	83.5	11.2	0.97	63.0	x5	1.22	1.2175
52	7/26/2024 8:28:52 AM	50	73.8	13.38	2057	4.05	889.0	889	0.0	231	88.2	178.1	11.8	0.97	63.3	x5	2.04	2.039375
53	7/26/2024 8:28:53 AM	51	73.8	16.00	1941	2.59	805.0	805	0.0	230	87.2	326.7	12.8	0.97	63.3	x5	3.03	3.025625
54	7/26/2024 8:28:54 AM	52	73.9	17.55	1828	1.73	720.0	720	0.0	217	86.1	524.1	13.9	0.97	63.9	x5	4.11	1
55	7/26/2024 8:28:55 AM	53	73.9	15.94	1717	2.64	643.0	643	0.0	164	87.5	321.1	12.5	0.97	63.9	x5	5.09	1
56	7/26/2024 8:28:56 AM	54	73.9	15.94	1603	2.65	587.0	587	0.0	164	87.7	321.1	12.3	0.97	63.9	x5	6.08	1
57	7/26/2024 8:28:57 AM	55	74.1	12.16	1485	4.78	570.1	570	0.1	130	88.8	139.2	11.2	0.97	63.7	x5	6.83	1
58	7/26/2024 8:28:58 AM	56	74.1	8.44	1361	6.87	591.9	589	2.9	101	89.3	67.7	10.7	0.97	63.7	x5	7.34	1
59	7/26/2024 8:28:59 AM	57	74.3	5.78	1234	8.37	635.2	631	4.2	79	89.5	38.2	10.5	0.97	63.5	x5	7.68	1
60	7/26/2024 8:29:00 AM	58	74.3	4.20	1104	9.26	690.3	685	5.3	68	89.6	25.2	10.4	0.97	63.5	x5	7.92	1
61	7/26/2024 8:29:01 AM	59	74.3	3.34	977	9.76	746.3	741	5.3	63	89.7	19.0	10.3	0.97	63.7	x5	8.10	1
62	7/26/2024 8:29:02 AM	60	74.5	2.82	860	10.05	803.1	797	6.1	64	89.7	15.6	10.3	0.97	63.7	x5	8.26	1
63	7/26/2024 8:29:03 AM	61	74.5	2.52	753	10.23	857.9	851	6.9	65	89.7	13.7	10.3	0.97	63.7	x5	8.39	1
64	7/26/2024 8:29:04 AM	62	74.8	2.35	659	10.33	910.3	902	8.3	69	89.8	12.6	10.2	0.97	63.9	x5	8.51	1
65	7/26/2024 8:29:05 AM	63	74.8	2.26	578	10.39	957.0	948	9.0	77	89.8	12.1	10.2	0.97	63.9	x5	8.57	1
66	7/26/2024 8:29:06 AM	64	74.8	2.22	512	10.42	998.5	989	9.5	84	89.8	11.9	10.2	0.97	63.9	x5	8.40	8.400625
67	7/26/2024 8:29:07 AM	65	75.2	2.19	454	10.44	1033.9	1024	9.9	99	89.8	11.7	10.2	0.97	64.0	x5	7.94	7.943125
68	7/26/2024 8:29:08 AM	66	75.2	2.19	407	10.44	1062.9	1053	9.9	99	89.8	11.7	10.2	0.97	64.0	x5	7.24	7.24375
69	7/26/2024 8:29:09 AM	67	75.2	2.21	367	10.43	1090.5	1080	10.5	103	89.9	11.8	10.1	0.97	64.2	x5	6.38	6.381875
70	7/26/2024 8:29:10 AM	68	75.2	2.22	334	10.43	1117.4	1107	10.4	108	89.9	11.9	10.1	0.97	64.2	x5	5.42	5.42375
71	7/26/2024 8:29:11 AM	69	75.6	2.18	309	10.45	1147.2	1137	10.2	112	89.9	11.7	10.1	0.97	64.4	x5	4.56	4.56375
72	7/26/2024 8:29:12 AM	70	75.6	2.08	285	10.51	1175.2	1165	10.2	116	89.9	11.1	10.1	0.97	64.4	x5	3.70	3.6975
73	7/26/2024 8:29:13 AM	71	75.6	1.98	264	10.57	1200.0	1190	10.0	118	89.9	10.4	10.1	0.97	64.4	x5	3.06	3.06125
74	7/26/2024 8:29:14 AM	72	75.7	1.90	247	10.62	1220.0	1210	10.0	118	89.9	10.0	10.1	0.97	64.2	x5	2.65	2.6525
75	7/26/2024 8:29:15 AM	73	75.7	1.86	47	10.65	1235.8	1226	9.8	118	90.0	9.8	10.0	0.97	64.2	x1	2.41	2.4075
76	7/26/2024 8:29:16 AM	74	76.1	1.87	44	10.65	1247.8	1238	9.8	120	89.9	9.8	10.1	0.97	63.9	x1	2.26	2.261875
77	7/26/2024 8:29:17 AM	75	76.1	1.92	41	10.62	1259.0	1249	10.0	130	89.9	10.1	10.1	0.97	63.9	x1	2.17	2.173125
78	7/26/2024 8:29:18 AM	76	76.3	2.00	38	10.58	1267.2	1257	10.2	132	89.9	10.6	10.1	0.97	63.7	x1	2.12	2.121875
79	7/26/2024 8:29:19 AM	77	76.3	2.00	36	10.58	1275.2	1265	10.2	132	89.9	10.6	10.1	0.97	63.7	x1	2.09	2.089375
80	7/26/2024 8:29:20 AM	78	76.5	1.99	34	10.58	1283.2	1273	10.2	133	89.9	10.5	10.1	0.97	64.0	x1	2.07	2.066875
81	7/26/2024 8:29:21 AM	79	76.5	1.94	32	10.61	1289.1	1280	9.1	134	89.9	10.2	10.1	0.97	64.0	x1	2.05	2.046875
82	7/26/2024 8:29:22 AM	80	76.8	1.89	32	10.64	1293.7	1285	8.7	133	89.9	10.0	10.1	0.97	64.4	x1	2.03	2.02625
83	7/26/2024 8:29:23 AM	81	76.8	1.87	32	10.65	1298.2	1290	8.2	135	89.9	9.8	10.1	0.97	64.8	x1	2.01	2.00625
84	7/26/2024 8:29:24 AM	82	76.8	1.84	34	10.67	1304.2	1296	8.2	135	89.9	9.6	10.1	0.97	64.8	x1	1.98	1.984375
85	7/26/2024 8:29:25 AM	83	77.0	1.81	37	10.69	1310.1	1302	8.1	135	89.9	9.5	10.1	0.97	64.9	x1	1.96	1.959375
86	7/26/2024 8:29:26 AM	84	77.0	1.82	41	10.68	1317.3	1309	8.3	136	89.9	9.5	10.1	0.97	64.9	x1	1.93	1.934375
87	7/26/2024 8:29:27 AM	85	77.0	1.84	45	10.67	1323.2	1315	8.2	137	89.9	9.6	10.1	0.97	64.9	x1	1.91	1.913125
88	7/26/2024 8:29:28 AM	86	77.0	1.83	49	10.67	1329.2	1321	8.2	140	89.9	9.6	10.1	0.97	64.9	x1	1.90	1.8975
89	7/26/2024 8:29:29 AM	87	77.2	1.81	53	10.68	1335.4	1327	8.4	140	89.9	9.5	10.1	0.97	64.4	x1	1.89	1.886875
90	7/26/2024 8:29:30 AM	88	77.0	1.83	56	10.67	1340.4	1332	8.4	140	89.9	9.6	10.1	0.97	64.2	x1	1.88	1.8825
91	7/26/2024 8:29:31 AM	89	77.0	1.84	59	10.66	1342.2	1334	8.2	141	89.9	9.7	10.1	0.97	64.2	x1	1.88	1.88125
92	7/26/2024 8:29:32 AM	90	77.2	1.82	61	10.67	1340.9	1333	7.9	140	89.9	9.6	10.1	0.97	64.2	x1	1.88	1.878125
93	7/26/2024 8:29:33 AM	91	77.2	1.78	63	10.70	1336.3	1328	8.3	138	89.9	9.3	10.1	0.97	64.2	x1	1.87	1.869375
94	7/26/2024 8:29:34 AM	92	77.2	1.74	65	10.72	1329.3	1321	8.3	138	89.9	9.1	10.1	0.97	64.2	x1	1.85	1.853125
95	7/26/2024 8:29:35 AM	93	77.2	1.76	67	10.71	1319.8	1311	8.8	137	89.9	9.2	10.1	0.97	64.2	x1	1.84	1.838125
96	7/26/2024 8:29:36 AM	94	77.2	1.80	69	10.69	1311.2	1302	9.2	136	89.9	9.4	10.1	0.97	64.2	x1	1.83	1.82625
97	7/26/2024 8:29:37 AM	95	77.4	1.82	70	10.68	1303.5	1294	9.5	136	89.9	9.5	10.1	0.97	64.0	x1	1.82	1.81875
98	7/26/2024 8:29:38 AM	96	77.4	1.78	71	10.70	1297.8	1288	9.8	135	89.9	9.3	10.1	0.97	64.0	x1	1.81	1.811875
99	7/26/2024 8:29:39 AM	97	77.4	1.76	72	10.71	1294.0	1284	10.0	134	89.9	9.2	10.1	0.97	64.2	x1	1.81	1.805
100	7/26/2024 8:29:40 AM	98	77.4	1.76	74	10.71	1293.2	1283	10.2	133	89.9	9.2	10.1	0.97	64.2	x1	1.80	1.8
101	7/26/2024 8:29:41 AM	99	77.5	1.74	76	10.72	1297.3	1287	10.3	133	89.9	9.1	10.1	0.97	64.0	x1	1.80	1.795625
102	7/26/2024 8:29:42 AM	100	77.5	1.74	79	10.72	1297.3	1287	10.3	133	89.9	9.1	10.1	0.97	64.0	x1	1.79	1.790625
103	7/26/2024 8:29:43 AM	101	77.7	1.74	84	10.72	1298.6	1288	10.6	133	89.9	9.1	10.1	0.97	64.2	x1	1.78	1.784375
104	7/26/2024 8:29:44 AM	102	77.9	1.77	89	10.70	1296.8	1286	10.8	132	89.9	9.3	10.1	0.97	64.0	x1	1.78	1.780625
105	7/26/2024 8:29:45 AM	103	77.9	1.80	96	10.68	1294.1	1283	11.1	132	89.9	9.4	10.1	0.97	64.0	x1	1.78	1.78
106	7/26/2024 8:29:46 AM	104	78.3	1.83	103	10.67	1290.1	1279	11.1	132	89.9	9.6	10.1	0.97	63.9	x1	1.78	1.78
107	7/26/2024 8:29:47 AM	105	78.3	1.85	109	10.65	1287.2	1276	11.2	132	89.9	9.7	10.1	0.97	63.9	x1	1.78	1.780625
108	7/26/2024 8:29:48 AM	106	78.4	1.86	115	10.65	1288.5	1277	11.5	132	89.9	9.8	10.1	0.97	63.5	x1	1.78	1.783125
109	7/26/2024 8:29:49 AM	107	78.4	1.86	121	10.65	1290.7	1279	11.7	132	89.8	9.8	10.2	0.97	63.5	x1	1.79	1.788125
110	7/26/2024 8:29:50 AM	108	78.4	1.87	126	10.64	1294.1	1282	12.1	132	89.8	9.8	10.2	0.97	63.5	x1	1.80	1.79625
111	7/26/2024 8:29:51 AM	109	78.6	1.87	132	10.64	1296.0	1284	12.0	131	89.8	9.8	10.2	0.96	63.5	x1	1.80	1.803125
112	7/26/2024 8:29:52 AM	110	78.6	1.81	138	10.67	1295.7	1284	11.7	130	89.8	9.5	10.2	0.96	63.5	x1	1.80	1.80375
113	7/26/2024 8:29:53 AM	111	78.6	1.72	146	10.72	1289.5	1278	11.5	129	89.8	9.0	10.2	0.97	63.7	x1	1.80	1.7975
114	7/26/2024 8:29:54 AM	112	78.6	1.65	155	10.77	1289.5	1278	11.5	129	89.8	8.6	10.2	0.97	63.7	x1	1.79	1.789375
115	7/26/2024 8:29:55 AM	113	78.6	1.60	163	10.79	1284.3	1273	11.3	128	89.8	8.3	10.2	0.97	63.9	x1	1.78	1.779375
116	7/26/2024 8:29:56 AM	114	78.6	1.65	171	10.76	1278.9	1269	9.9	125	89.9	8.6	10.1	0.96	64.9	x1	1.77	1.7725
117	7/26/2024 8:29:57 AM	115	78.6	2.55	178	10.26	1271.1	1264	7.1	132	89.9	13.9	10.1	0.96	64.9	x1	1.82	1.823125
118	7/26/2024 8:29:58 AM	116	78.8	5.75	184	8.47	1255.1	1248	7.1	132	89.8	37.9	10.2	0.96	65.5	x1	2.07	2.07375
119	7/26/2024 8:29:59 AM	117	78.8	10.09	189	6.04	1206.4	1202	4.4	124	89.6	93.3	10.4	0.96	65.5	x1	2.60	2.595625
120	7/26/2024 8:30:00 AM	118	78.6	13.73	193	4.00	1125.6	1123	2.6	151	89.4	191.4	10.6	0.97	65.3	x1	3.34	3.343125
121	7/26/2024 8:30:01 AM	119	78.6	16.14	196	2.65	1027.1	1024	3.1	215	89.0	339.0	11.0	0.97	65.3	x1	4.24	1
122	7/26/2024 8:30:02 AM	120	78.4	16.71	198	2.33	928.9	922	6.9	176	88.8	398.7	11.2	0.97	64.8	x1	5.17	1
123	7/26/2024 8:30:03 AM	121	78.3	14.45	197	3.60	854.6	845	9.6	139	89.3	224.0	10.7	0.97	64.6	x1	5.96	1
124	7/26/2024 8:30:04 AM	122	78.3	10.64	193	5.73	876.9	866	10.9	133	89.6	103.8	10.4	0.97	64.6	x1	6.51	1
125	7/26/2024 8:30:05 AM	123	78.4	7.21	185	7.65	876.9	866	10.9	133	89.7	52.6	10.3	0.97	64.2	x1	6.84	1
126	7/26/2024 8:30:06 AM	124	78.4	4.84	174	8.98	956.4	945	11.4	108	89.8	30.2	10.2	0.97	64.2	x1	7.03	1
127	7/26/2024 8:30:07 AM	125	78.6	3.42	160	9.77	1050.2	1038	12.2	74	89.8	19.5	10.2	0.97	64.4	x1	7.12	1
128	7/26/2024 8:30:08 AM	126	78.6	2.60	146	10.23	1144.4	1132	12.4	64	89.8	14.2	10.2	0.97	64.4	x1	7.17	7.171875
129	7/26/2024 8:30:09 AM	127	78.8	2.12	134	10.51	1231.4	1219	12.4	64	89.9	11.3	10.1	0.97	64.4	x1	7.20	7.196875
130	7/26/2024 8:30:10 AM	128	79.0	1.84	124	10.66	1307.6	1295	12.6	65	89.9	9.6	10.1	0.97	64.2	x1	7.21	7.20875
131	7/26/2024 8:30:11 AM	129	79.0	1.68	119	10.75	1372.7	1360	12.7	65	89.9	8.7	10.1	0.97	64.2	x1	7.21	7.21375
132	7/26/2024 8:30:12 AM	130	79.2	1.59	118	10.80	1428.2	1415	13.2	70	89.9	8.2	10.1	0.97	64.2	x1	7.21	7.21
133	7/26/2024 8:30:13 AM	131	79.2	1.56	121	10.82	1473.6	1460	13.6	74	89.9	8.1	10.1	0.97	64.2	x1	7.15	7.148125
134	7/26/2024 8:30:14 AM	132	79.3	1.58	128	10.81	1512.9	1499	13.9	79	89.8	8.2	10.2	0.97	64.0	x1	6.89	6.8875
testo

Cell Formulas
Range	Formula
Q30:Q134	Q30	=AVERAGE(D15:D30)
R30:R134	R30	=IF(AND(Q30>3,Q29>3,(Q30-Q29)>0.05),1,Q30)

 

[felixstraube]

Happy to help!