Help converting 1-min data to 15-min data

[MarkShepherd]

Hi - does anyone know how to convert date/time stamped data in a column in 1-min intervals into a column that has date/time stamped data at 15-min averages? At the moment the only way I know is to delete the times between the 15-minute intervals and then take an average of the data to provide a 15-min average, but this is taking ages!

Time Stamp	LAN15_DM Flow (l/s)
01/08/2022 00:00	4,20
01/08/2022 00:01	3,70
01/08/2022 00:02	4,00
01/08/2022 00:03	3,80
01/08/2022 00:04	3,80
01/08/2022 00:05	4,00
01/08/2022 00:06	4,50
01/08/2022 00:07	4,80
01/08/2022 00:08	4,20
01/08/2022 00:09	4,20
01/08/2022 00:10	4,20
01/08/2022 00:11	3,80
01/08/2022 00:12	4,00
01/08/2022 00:13	3,80
01/08/2022 00:14	4,00
01/08/2022 00:15	4,30
01/08/2022 00:16	4,20
01/08/2022 00:17	4,20
01/08/2022 00:18	4,00
01/08/2022 00:19	4,70
01/08/2022 00:20	4,00
01/08/2022 00:21	4,20
01/08/2022 00:22	4,00
01/08/2022 00:23	4,30
01/08/2022 00:24	4,30
01/08/2022 00:25	4,50
01/08/2022 00:26	4,20
01/08/2022 00:27	3,70
01/08/2022 00:28	4,00
01/08/2022 00:29	4,00
01/08/2022 00:30	4,00

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C	D	E
2	1/8/2022 0:00	4.20		1/8/2022 0:00	4.07
3	1/8/2022 0:01	3.70		1/8/2022 0:15	4.17
4	1/8/2022 0:02	4.00
5	1/8/2022 0:03	3.80
6	1/8/2022 0:04	3.80
7	1/8/2022 0:05	4.00
8	1/8/2022 0:06	4.50
9	1/8/2022 0:07	4.80
10	1/8/2022 0:08	4.20
11	1/8/2022 0:09	4.20
12	1/8/2022 0:10	4.20
13	1/8/2022 0:11	3.80
14	1/8/2022 0:12	4.00
15	1/8/2022 0:13	3.80
16	1/8/2022 0:14	4.00
17	1/8/2022 0:15	4.30
18	1/8/2022 0:16	4.20
19	1/8/2022 0:17	4.20
20	1/8/2022 0:18	4.00
21	1/8/2022 0:19	4.70
22	1/8/2022 0:20	4.00
23	1/8/2022 0:21	4.20
24	1/8/2022 0:22	4.00
25	1/8/2022 0:23	4.30
26	1/8/2022 0:24	4.30
27	1/8/2022 0:25	4.50
28	1/8/2022 0:26	4.20
29	1/8/2022 0:27	3.70
30	1/8/2022 0:28	4.00
31	1/8/2022 0:29	4.00
32	1/8/2022 0:30	4.00
Sheet11

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(A2:A32,SEQUENCE(ROWS(A2:A32)/15,1,1,15))
E2:E3	E2	=AVERAGE(FILTER($B$2:$B$32,($A$2:$A$32>=D2)*($A$2:$A$32<D2+(15/60/24))))
Dynamic array formulas.

 

[MarkShepherd]

Thanks James, but that didn't work as the formulae are not accepted. I see that your platform is Windows while mine is iOS - would that make a difference?

 

[JamesCanale]

I didn't notice that... Likely the problem. I'm just not seeing the function that isn't supported by mac.

I had to change your 4,2 to regular American 4.2 - for example. Are the numbers really numbers and the dates really dates?

 

[MarkShepherd]

Found the problem - the dates are dates, but there were some numbers in the columns that when I changed to "." didn't change for some reason and remained as ",". Fixed it and the formulae work fine. Thanks!