Converting data from 30 minute to 15 minute intervals

[dfariba]

Hello

I want to convert my data from 30 minute to 15 minute intervals, my data start from 12:20 AM to 11:50 PM (Namur weather) and I want to convert it to start from 12:00 AM to 11:45PM just like solar power data time (Likw solar power Data time that I upload). can any one help me how to do so?

Thanks for your consideration
Fariba

 

[Toadstool]

Hi DFariba,

Any such change will of course be an approximation. You can say that your first set of values for 15 minute will always be the first set of values of 30 minute, because nothing better is available.

After that I've used MATCH and AVERAGEA on most cells, like Temp., so it averages the previous 15 minute value with the 30 minute value for the same, or latest time less than the 15 minute time. If it finds no match then it just uses the previous 15 minute time value.

Text fields like Weather it just looks for the latest matching time in the 30 minute list.

DFariba.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	30 Min Intervals	Temp.	Weather	Wind (km/h)	Humidity	Barometer	Visibilty km			15 Min Intervals	Temp.	Weather	Wind (km/h)	Humidity	Barometer	Visibilty km
2	12:20:00 AM	12	Passing Clouds	4.9	0.82	1004.92	N/A			12:00:00 AM	12.00	Passing Clouds	4.90	0.82	1004.92	N/A
3	12:50:00 AM	11	Clouds1	5.74	0.79	1005.75	N/A			12:15:00 AM	12.00	Passing Clouds	4.90	0.82	1004.92	N/A
4	1:20:00 AM	10	Clouds2	6.32	0.77	1006.33	12			12:30:00 AM	12.00	Passing Clouds	4.90	0.82	1004.92	N/A
5	1:50:00 AM	9	Clouds3	6.71	0.76	1006.71	13			12:45:00 AM	12.00	Passing Clouds	4.90	0.82	1004.92	N/A
6	2:20:00 AM	8	Clouds4	6.93	0.75	1006.93	13			1:00:00 AM	11.50	Clouds1	5.32	0.81	1005.34	N/A
7	2:50:00 AM	7	Clouds5	7	0.75	1007	14			1:15:00 AM	11.25	Clouds1	5.53	0.80	1005.54	N/A
8	3:20:00 AM	6	Clouds6	6.93	0.75	1006.93	13			1:30:00 AM	10.63	Clouds2	5.93	0.78	1005.94	12
9	3:50:00 AM	5	Clouds7	6.71	0.76	1006.71	13			1:45:00 AM	10.31	Clouds2	6.12	0.78	1006.13	12
10	4:20:00 AM	4	Clouds8	6.32	0.77	1006.33	12			2:00:00 AM	9.66	Clouds3	6.42	0.77	1006.42	13
11	4:50:00 AM	3	Clouds9	5.74	0.79	1005.75	11			2:15:00 AM	9.33	Clouds3	6.56	0.76	1006.57	13
12	5:20:00 AM	4	Clouds10	6.93	0.75	1006.93	13			2:30:00 AM	8.66	Clouds4	6.75	0.76	1006.75	13
13	5:50:00 AM	5	Clouds11	8.06	0.72	1008.06	16			2:45:00 AM	8.33	Clouds4	6.84	0.75	1006.84	13
14	6:20:00 AM	6	Clouds12	9.17	0.7	1009.17	18			3:00:00 AM	7.67	Clouds5	6.92	0.75	1006.92	14
15	6:50:00 AM	7	Clouds13	10.25	0.68	1010.25	N/A			3:15:00 AM	7.33	Clouds5	6.96	0.75	1006.96	14
16	7:20:00 AM	8	Clouds14	11.31	0.67	1011.31	22			3:30:00 AM	6.67	Clouds6	6.94	0.75	1006.94	13
17	7:50:00 AM	9	Clouds15	12.37	0.65	1012.37	24			3:45:00 AM	6.33	Clouds6	6.94	0.75	1006.94	13
18	8:20:00 AM	10	Clouds16	13.42	0.64	1013.42	26			4:00:00 AM	5.67	Clouds7	6.82	0.76	1006.82	13
19	8:50:00 AM	11	Clouds17	14.46	0.63	1014.46	28			4:15:00 AM	5.33	Clouds7	6.77	0.76	1006.77	13
20	9:20:00 AM	12	Clouds18	15.49	0.62	1015.49	30			4:30:00 AM	4.67	Clouds8	6.54	0.76	1006.55	12
1st

Cell Formulas
Range	Formula
K2:P2	K2	=B2
K3:K20	K3	=AVERAGEA(K2,IFERROR(INDEX(B$2:B$50,MATCH($J3,$A$2:$A$50,1)),B$2))
L3:L20,P3:P20	L3	=IFERROR(INDEX(C$2:C$50,MATCH($J3,$A$2:$A$50,1)),L2)
M3:O20	M3	=AVERAGEA(M2,IFERROR(INDEX(D$2:D$50,MATCH($J3,$A$2:$A$50,1)),M2))

 

[dfariba]

Thanks for your response, It really help me.

 

[Toadstool]

You're welcome.