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]

What data point do you want to be taken as the spike? The maximum of the spike? The first value that is greater than 15?
In how many data points in (in front and after) would we be looking for this conditions?

What if you have something like this?:

What would be the spike point?
Or would something like this never happen?

 

[dmheller]

I am looking to find the spike go back 20 seconds or 20 data points then average the 20th back to the 10th back.
that spike you show might happen. Its hard to say. All our data we are looking for would be less than 8 though. The spike would not come below 8 unless we caused it to.

 

[felixstraube]

So what point would we consider the spike then?

All our data we are looking for would be less than 8 though.

What do you mean with "less than 8"?

 

[felixstraube]

would this work?:

We search for the first value over 15, and take that as the spike point. Than go back 20 data points and take 10. I colored the datapoints that are averaged by the formula.
Is this correct? Let me know.

Column T is just for control.

Book4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date Time			Header Col D															Average from 20 to 10 data points before the first value over 15	Control (delete)
2	2024-07-26 08:28:30	28	74.3	0.25	2466	11.37	1033	1033	0	154	89.3	1.2	10.7	0.97	63.1	x5	7.5556	1.8975
3	2024-07-26 08:28:31	29	74.3	0.21	2351	11.4	1031	1031	0	154	89.3	1	10.7	0.97	63.1	x5	7.7963	1.8869
4	2024-07-26 08:28:32	30	74.3	0.16	2236	11.44	1029	1029	0	155	89.4	0.8	10.6	0.97	63.1	x5	7.9825	1.8825
5	2024-07-26 08:28:33	31	74.3	0.13	2126	11.44	1027	1027	0	157	89.4	0.8	10.6	0.97	63.1	x5	8.1356	1.8813
6	2024-07-26 08:28:34	32	74.3	0.16	2033	11.45	1025	1025	0	159	89.4	0.8	10.6	0.97	63.1	x5	8.2694	1.8781
7	2024-07-26 08:28:35	33	74.3	0.2	1967	11.44	1024	1024	0	161	89.4	0.9	10.6	0.97	63.1	x5	8.39	1.8694
8	2024-07-26 08:28:36	34	74.3	0.23	1934	11.42	1024	1024	0	163	89.5	1.1	10.5	0.97	63.3	x5	8.4444	1.8531
9	2024-07-26 08:28:37	35	74.3	0.22	1939	11.42	1024	1024	0	164	89.5	1.1	10.5	0.97	63.3	x5	8.2794	1.8381
10	2024-07-26 08:28:38	36	74.1	0.22	1984	11.42	1025	1025	0	165	89.4	1.1	10.6	0.97	63.3	x5	7.8219	1.8263
11	2024-07-26 08:28:39	37	74.1	0.19	2061	11.43	1025	1025	0	167	89.4	0.9	10.6	0.97	63.3	x5	7.1225	1.8188
12	2024-07-26 08:28:40	38	74.1	0.18	2157	11.43	1024	1024	0	168	89.4	0.9	10.6	0.97	63.3	x5	6.2606	1.8119
13	2024-07-26 08:28:41	39	74.1	0.2	2249	11.41	1024	1024	0	169	89.4	1	10.6	0.97	63.3	x5	5.3025	1.805
14	2024-07-26 08:28:42	40	74.1	0.25	2319	11.38	1025	1025	0	170	89.4	1.2	10.6	0.96	63.3	x5	4.5638	1.8
15	2024-07-26 08:28:43	41	74.1	0.35	2358	11.32	1027	1027	0	170	89.3	1.7	10.7	0.97	63.1	x5	3.6975	1.7956
16	2024-07-26 08:28:44	42	74.1	0.35	2376	11.32	1030	1030	0	170	89.3	1.7	10.7	0.97	63.1	x5	3.0613	1.7906
17	2024-07-26 08:28:45	43	74.1	0.36	2394	11.31	1032	1032	0	171	89.3	1.7	10.7	0.97	63	x5	2.6525	1.7844
18	2024-07-26 08:28:46	44	74.1	0.37	2406	11.31	1032	1032	0	171	89.3	1.8	10.7	0.97	63	x5	2.4075	1.7806
19	2024-07-26 08:28:47	45	74.1	0.38	2409	11.3	1032	1032	0	172	89.3	1.8	10.7	0.97	63.1	x5	2.2619	1.78
20	2024-07-26 08:28:48	46	74.1	0.42	2389	11.28	1031	1031	0	173	89.3	2	10.7	0.97	63	x5	2.1731	1.78
21	2024-07-26 08:28:49	47	74.1	1.39	2343	10.74	1027	1027	0	172	89.3	7.1	10.7	0.97	63	x5	2.1219	1.7806
22	2024-07-26 08:28:50	48	73.8	4.86	2266	8.8	1007	1007	0	175	89.1	30.3	10.9	0.97	63	x5	2.0894	1.7831
23	2024-07-26 08:28:51	49	73.8	9.51	2168	6.21	960	960	0	199	88.8	83.5	11.2	0.97	63	x5	2.0669	1.7881
24	2024-07-26 08:28:52	50	73.8	13.38	2057	4.05	889	889	0	231	88.2	178.1	11.8	0.97	63.3	x5	2.0469	1.7963
25	2024-07-26 08:28:53	51	73.8	16	1941	2.59	805	805	0	230	87.2	326.7	12.8	0.97	63.3	x5	2.0263	1.8031	0.198	0.198
26	2024-07-26 08:28:54	52	73.9	17.55	1828	1.73	720	720	0	217	86.1	524.1	13.9	0.97	63.9	x5	2.0063	1.8038
27	2024-07-26 08:28:55	53	73.9	14	1717	2.64	643	643	0	164	87.5	321.1	12.5	0.97	63.9	x5	1.9844	1.7975
28	2024-07-26 08:28:56	54	73.9	15.94	1603	2.65	587	587	0	164	87.7	321.1	12.3	0.97	63.9	x5	1.9594	1.7894
29	2024-07-26 08:28:57	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	1.9344	1.7794
30	2024-07-26 08:28:58	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	1.9131	1.7725
31	2024-07-26 08:28:59	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	1.8975	1.8231
32	2024-07-26 08:29:00	58	74.3	4.2	1104	9.26	690.3	685	5.3	68	89.6	25.2	10.4	0.97	63.5	x5	1.8869	2.0738
33	2024-07-26 08:29:01	59	74.3	3.34	977	9.76	746.3	741	5.3	63	89.7	19	10.3	0.97	63.7	x5	1.8825	2.5956
34	2024-07-26 08:29:02	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	1.8813	3.3431
35	2024-07-26 08:29:03	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	1.8781	1
36	2024-07-26 08:29:04	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	1.8694	1
37	2024-07-26 08:29:05	63	74.8	2.26	578	10.39	957	948	9	77	89.8	12.1	10.2	0.97	63.9	x5	1.8531	1
38	2024-07-26 08:29:06	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	1.8381	1
39	2024-07-26 08:29:07	65	75.2	2.19	454	10.44	1033.9	1024	9.9	99	89.8	11.7	10.2	0.97	64	x5	1.8263	1
40	2024-07-26 08:29:08	66	75.2	2.19	407	10.44	1062.9	1053	9.9	99	89.8	11.7	10.2	0.97	64	x5	1.8188	1
41	2024-07-26 08:29:09	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	1.8119	1
42	2024-07-26 08:29:10	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	1.805	7.1719
43	2024-07-26 08:29:11	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	1.8	7.1969
44	2024-07-26 08:29:12	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	1.7956	7.2088
45	2024-07-26 08:29:13	71	75.6	1.98	264	10.57	1200	1190	10	118	89.9	10.4	10.1	0.97	64.4	x5	1.7906	7.2138
46	2024-07-26 08:29:14	72	75.7	1.9	247	10.62	1220	1210	10	118	89.9	10	10.1	0.97	64.2	x5	1.7844	7.21
47	2024-07-26 08:29:15	73	75.7	1.86	47	10.65	1235.8	1226	9.8	118	90	9.8	10	0.97	64.2	x1	1.7806	7.1481
48	2024-07-26 08:29:16	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	1.78	6.8875
49	2024-07-26 08:29:17	75	76.1	1.92	41	10.62	1259	1249	10	130	89.9	10.1	10.1	0.97	63.9	x1	1.78	6.674
50	2024-07-26 08:29:18	76	76.3	2	38	10.58	1267.2	1257	10.2	132	89.9	10.6	10.1	0.97	63.7	x1	1.7806	6.17
51	2024-07-26 08:29:19	77	76.3	2	36	10.58	1275.2	1265	10.2	132	89.9	10.6	10.1	0.97	63.7	x1	1.7831	5.4031
52	2024-07-26 08:29:20	78	76.5	1.99	34	10.58	1283.2	1273	10.2	133	89.9	10.5	10.1	0.97	64	x1	1.7881	4.4608
53	2024-07-26 08:29:21	79	76.5	1.94	32	10.61	1289.1	1280	9.1	134	89.9	10.2	10.1	0.97	64	x1	1.7963	3.5527
54	2024-07-26 08:29:22	80	76.8	1.89	32	10.64	1293.7	1285	8.7	133	89.9	10	10.1	0.97	64.4	x1	1.8031	2.844
55	2024-07-26 08:29:23	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	1.8038	2.3589
56	2024-07-26 08:29:24	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.7975	2.0488
57	2024-07-26 08:29:25	83	77	1.81	37	10.69	1310.1	1302	8.1	135	89.9	9.5	10.1	0.97	64.9	x1	1.7894	1.8529
58	2024-07-26 08:29:26	84	77	1.82	41	10.68	1317.3	1309	8.3	136	89.9	9.5	10.1	0.97	64.9	x1	1.7794	1.7283
59	2024-07-26 08:29:27	85	77	1.84	45	10.67	1323.2	1315	8.2	137	89.9	9.6	10.1	0.97	64.9	x1	1.7725	1.65
60	2024-07-26 08:29:28	86	77	1.83	49	10.67	1329.2	1321	8.2	140	89.9	9.6	10.1	0.97	64.9	x1	1.8231	1.6025
61	2024-07-26 08:29:29	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	2.0738	1.5767
62	2024-07-26 08:29:30	88	77	1.83	56	10.67	1340.4	1332	8.4	140	89.9	9.6	10.1	0.97	64.2	x1	2.5956	1.57
63	2024-07-26 08:29:31	89	77	1.84	59	10.66	1342.2	1334	8.2	141	89.9	9.7	10.1	0.97	64.2	x1	3.3431	1.58
64	2024-07-26 08:29:32	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	4.2394	######
65	2024-07-26 08:29:33	91	77.2	1.78	63	10.7	1336.3	1328	8.3	138	89.9	9.3	10.1	0.97	64.2	x1	5.1694	######
66	2024-07-26 08:29:34	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	5.9569	######
67	2024-07-26 08:29:35	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	6.5056	######
68	2024-07-26 08:29:36	94	77.2	1.8	69	10.69	1311.2	1302	9.2	136	89.9	9.4	10.1	0.97	64.2	x1	6.84	######
69	2024-07-26 08:29:37	95	77.4	1.82	70	10.68	1303.5	1294	9.5	136	89.9	9.5	10.1	0.97	64	x1	7.0256	######
70	2024-07-26 08:29:38	96	77.4	1.78	71	10.7	1297.8	1288	9.8	135	89.9	9.3	10.1	0.97	64	x1	7.1225	######
71	2024-07-26 08:29:39	97	77.4	1.76	72	10.71	1294	1284	10	134	89.9	9.2	10.1	0.97	64.2	x1	7.1719	######
72	2024-07-26 08:29:40	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	7.1969	######
73	2024-07-26 08:29:41	99	77.5	1.74	76	10.72	1297.3	1287	10.3	133	89.9	9.1	10.1	0.97	64	x1	7.2088	######
74	2024-07-26 08:29:42	100	77.5	1.74	79	10.72	1297.3	1287	10.3	133	89.9	9.1	10.1	0.97	64	x1	7.2138	######
75	2024-07-26 08:29:43	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	7.21	######
76	2024-07-26 08:29:44	102	77.9	1.77	89	10.7	1296.8	1286	10.8	132	89.9	9.3	10.1	0.97	64	x1	7.1481	######
77	2024-07-26 08:29:45	103	77.9	1.8	96	10.68	1294.1	1283	11.1	132	89.9	9.4	10.1	0.97	64	x1	6.8875	######
78	2024-07-26 08:29:46	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	6.674	######
79	2024-07-26 08:29:47	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	6.17	0
80	2024-07-26 08:29:48	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	5.4031	0
81	2024-07-26 08:29:49	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	4.4608	0
82	2024-07-26 08:29:50	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	3.5527	0
83	2024-07-26 08:29:51	109	78.6	1.87	132	10.64	1296	1284	12	131	89.8	9.8	10.2	0.96	63.5	x1	2.844	0
84	2024-07-26 08:29:52	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	2.3589	0
85	2024-07-26 08:29:53	111	78.6	1.72	146	10.72	1289.5	1278	11.5	129	89.8	9	10.2	0.97	63.7	x1	2.0488	0
86	2024-07-26 08:29:54	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.8529	0
87	2024-07-26 08:29:55	113	78.6	1.6	163	10.79	1284.3	1273	11.3	128	89.8	8.3	10.2	0.97	63.9	x1	1.7283	0
88	2024-07-26 08:29:56	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.65	0
89	2024-07-26 08:29:57	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.6025	0
90	2024-07-26 08:29:58	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	1.5767	0
91	2024-07-26 08:29:59	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	1.57	0
92	2024-07-26 08:30:00	118	78.6	13.73	193	4	1125.6	1123	2.6	151	89.4	191.4	10.6	0.97	65.3	x1	1.58	0
93	2024-07-26 08:30:01	119	78.6	16.14	196	2.65	1027.1	1024	3.1	215	89	339	11	0.97	65.3	x1	######	0	1.806	1.806
94	2024-07-26 08:30:02	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	######	0
95	2024-07-26 08:30:03	121	78.3	14.45	197	3.6	854.6	845	9.6	139	89.3	224	10.7	0.97	64.6	x1	######	0
96	2024-07-26 08:30:04	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	######	0
97	2024-07-26 08:30:05	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	######	0
98	2024-07-26 08:30:06	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	######	0
99	2024-07-26 08:30:07	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	######	0
100	2024-07-26 08:30:08	126	78.6	2.6	146	10.23	1144.4	1132	12.4	64	89.8	14.2	10.2	0.97	64.4	x1	######	0
101	2024-07-26 08:30:09	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	######	0
102	2024-07-26 08:30:10	128	79	1.84	124	10.66	1307.6	1295	12.6	65	89.9	9.6	10.1	0.97	64.2	x1	######	0
103	2024-07-26 08:30:11	129	79	1.68	119	10.75	1372.7	1360	12.7	65	89.9	8.7	10.1	0.97	64.2	x1	######	0
104	2024-07-26 08:30:12	130	79.2	1.59	118	10.8	1428.2	1415	13.2	70	89.9	8.2	10.1	0.97	64.2	x1	######	0
105	2024-07-26 08:30:13	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	######	0
106	2024-07-26 08:30:14	132	79.3	1.58	128	10.81	1512.9	1499	13.9	79	89.8	8.2	10.2	0.97	64	x1	######	0
Sheet2

Cell Formulas
Range	Formula
Q2:Q106	Q2	=AVERAGE(D16:D31)
R2:R106	R2	=IF(AND(Q31>3,Q30>3,(Q31-Q30)>0.05),1,Q31)
S2:S106	S2	=LET( r, D2, s, SEQUENCE(21,,-20), d, N(OFFSET(r, s, 0, 1, 1)), max, MAX(DROP(d, -1)), IFERROR(IF((r >= 15)*(max < 15), AVERAGE(TAKE(d, 10)), NA()), "") )
T25,T93	T25	=AVERAGE(D5:D14)

 

[dmheller]

thank you very much for this. This will help. I had been doing it with around 6 helper columns and it was a mess.

 

[felixstraube]

Thanks for the feedback. Happy to help.

 

[dmheller]

Thanks for the feedback. Happy to help

In my efforts to learn, i been looking at the equation and it is great. I tried to use the same criteria and get an average of column E if the same is triggered. I see it is based on equation names and other things but i cant get it to work. I am sure you have a quick fix for this maybe. How would i get the same 10 data point average for column e as well?

 

[felixstraube]

in T2 for example try this and copy down:

=LET(
r, D2,
s, SEQUENCE(21,,-20),
d, N(OFFSET(r, s, 0, 1, 1)),
v, N(OFFSET(r, s, 1, 1, 1)),
max, MAX(DROP(d, -1)),
IFERROR(IF((r >= 15)*(max < 15), AVERAGE(TAKE(v, 10)), NA()), "")
)

 

[dmheller]

in T2 for example try this and copy down:

=LET(
r, D2,
s, SEQUENCE(21,,-20),
d, N(OFFSET(r, s, 0, 1, 1)),
v, N(OFFSET(r, s, 1, 1, 1)),
max, MAX(DROP(d, -1)),
IFERROR(IF((r >= 15)*(max < 15), AVERAGE(TAKE(v, 10)), NA()), "")
)

that's it, thank you.