Summing consecutive values according to specified time lengths

[ian.cook]

Hello everyone,

I am analyzing time-stamped, free-living accelerometry data, which records accelerometry counts (column C) and steps (column D) every second.

I've constructed a spreadsheet which allows me to capture the number of bouts (column N) of specified continuous time (column M). So for instance, for valid data, there are 30 bouts of 5 minutes of continuous activity (cells M7..N7).

I've managed to cobble together a way of summing the continuous step bout data (column K) in column L. However, I would prefer to add a column O such that I can get the number of steps accumulated for the various specified continuous bout lengths. For instance, how man steps are accumulated during the 30 bouts of 5 minutes of continuous step activity?

Your assistance would be much appreciated.

Kind regards,
Ian

Step Analysis_DHDSS_long1.2_example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													Bouts
2	DATE	TIME	VALUE	STEPS	EXCL	ACCEL	1: =0 Sed; 0: >=1 steps (Act)	lag	b1	break	STEPS		Length (min)	Frequency	Step total
3	07-Jan-04	07:53	417	16	FALSE	0.1	0				417		1	211	?????
4	07-Jan-04	07:54	1702	78	FALSE	0.63	0	0	0		1702		2	89
5	07-Jan-04	07:55	2486	100	FALSE	1.11	0	0	0		2486		3	48
6	07-Jan-04	07:56	2922	106	FALSE	1.38	0	0	0		2922		4	42
7	07-Jan-04	07:57	2896	108	FALSE	1.39	0	0	0		2896		5	30
8	07-Jan-04	07:58	3277	106	FALSE	1.54	0	0	0		3277		6	20
9	07-Jan-04	07:59	2739	100	FALSE	1.22	0	0	0		2739		7	15
10	07-Jan-04	08:00	2440	99	FALSE	1.08	0	0	0		2440		8	10
11	07-Jan-04	08:01	789	21	FALSE	0.19	0	0	0		789		9	12
12	07-Jan-04	08:02	1174	29	FALSE	0.3	0	0	0		1174		10	5
13	07-Jan-04	08:03	1777	10	FALSE	0.4	0	0	0		1777		11	10
14	07-Jan-04	08:04	233	14	FALSE	0.05	0	0	0		233		12	5
15	07-Jan-04	08:05	157	13	FALSE	0.04	0	0	0		157		13	4
16	07-Jan-04	08:06	114	11	FALSE	0.03	0	0	0		114	23123	14	8
17	07-Jan-04	08:07	0	0	FALSE	0.03	1	0	1				15	6
18	07-Jan-04	08:08	0	0	FALSE	0.02	1	1	0				16	3
19	07-Jan-04	08:09	0	0	FALSE	0.05	1	1	0				17	3
20	07-Jan-04	08:10	0	0	FALSE	0.04	1	1	0				18	1
21	07-Jan-04	08:11	0	0	FALSE	0.07	1	1	0				19	8
22	07-Jan-04	08:12	0	0	FALSE	0.05	1	1	0				20	3
23	07-Jan-04	08:13	0	0	FALSE	0.02	1	1	0				21	0
24	07-Jan-04	08:14	469	0	FALSE	0.11	1	1	0				22	4
25	07-Jan-04	08:15	267	28	FALSE	0.07	0	1	-1	1	267		23	2
26	07-Jan-04	08:16	126	15	FALSE	0.03	0	0	0		126		24	2
27	07-Jan-04	08:17	570	37	FALSE	0.15	0	0	0		570		25	1
28	07-Jan-04	08:18	514	40	FALSE	0.14	0	0	0		514		26	1
29	07-Jan-04	08:19	1027	74	FALSE	0.37	0	0	0		1027		27	3
30	07-Jan-04	08:20	544	35	FALSE	0.14	0	0	0		544	3048	28	1
Analysis

Cell Formulas
Range	Formula
G3:G30	G3	=IF(A3="","",IF(AND(E3=FALSE,D3>=1),0,IF(AND(E3=FALSE,D3=0),1,"")))
K3:K30	K3	=IF(AND(E3=FALSE,G3=0),C3,"")
L3:L30	L3	=IF(AND(K3<>"",K4=""),SUM(K$2:K4)-SUM(L$2:L2),"")
H4:H30	H4	=IF(G4="","",G3)
I4:I30	I4	=IF(H4="","",G4-H4)
J4:J30	J4	=IF(H4="","",IF(I4<0,ABS(I4),""))
N3:N30	N3	=SUM(IF(FREQUENCY(IF(G$3:G$17282=0,ROW(G$3:G$17282)),IF(G$3:G$17282<>0,ROW(G$3:G$17282)))=M3,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CA_Punit]

Do you want for Lenght 2 Step total to be 16+78.

If not then please post a sample answer you would require.

 

[ian.cook]

I've added some sample answers:

Step Analysis_DHDSS_long1.2_example_answers.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													Bouts
2	DATE	TIME	VALUE	STEPS	EXCL	ACCEL	1: =0 Sed; 0: >=1 steps (Act)	lag	b1	break	STEPS		Length (min)	Frequency	Step total
3	07-Jan-04	07:53	417	16	FALSE	0.1	0				417		1	0
4	07-Jan-04	07:54	1702	78	FALSE	0.63	0	0	0		1702		2	0
5	07-Jan-04	07:55	2486	100	FALSE	1.11	0	0	0		2486		3	0
6	07-Jan-04	07:56	2922	106	FALSE	1.38	0	0	0		2922		4	0
7	07-Jan-04	07:57	2896	108	FALSE	1.39	0	0	0		2896		5	0
8	07-Jan-04	07:58	3277	106	FALSE	1.54	0	0	0		3277		6	1	3048
9	07-Jan-04	07:59	2739	100	FALSE	1.22	0	0	0		2739		7	0
10	07-Jan-04	08:00	2440	99	FALSE	1.08	0	0	0		2440		8	0
11	07-Jan-04	08:01	789	21	FALSE	0.19	0	0	0		789		9	1	3557
12	07-Jan-04	08:02	1174	29	FALSE	0.3	0	0	0		1174		10	0
13	07-Jan-04	08:03	1777	10	FALSE	0.4	0	0	0		1777		11	0
14	07-Jan-04	08:04	233	14	FALSE	0.05	0	0	0		233		12	0
15	07-Jan-04	08:05	157	13	FALSE	0.04	0	0	0		157		13	0
16	07-Jan-04	08:06	114	11	FALSE	0.03	0	0	0		114	23123	14	1	23123
17	07-Jan-04	08:07	0	0	FALSE	0.03	1	0	1				15	0
18	07-Jan-04	08:08	0	0	FALSE	0.02	1	1	0				16	0
19	07-Jan-04	08:09	0	0	FALSE	0.05	1	1	0				17	0
20	07-Jan-04	08:10	0	0	FALSE	0.04	1	1	0				18	0
21	07-Jan-04	08:11	0	0	FALSE	0.07	1	1	0				19	0
22	07-Jan-04	08:12	0	0	FALSE	0.05	1	1	0				20	0
23	07-Jan-04	08:13	0	0	FALSE	0.02	1	1	0				21	0
24	07-Jan-04	08:14	469	0	FALSE	0.11	1	1	0				22	0
25	07-Jan-04	08:15	267	28	FALSE	0.07	0	1	-1	1	267		23	0
26	07-Jan-04	08:16	126	15	FALSE	0.03	0	0	0		126		24	0
27	07-Jan-04	08:17	570	37	FALSE	0.15	0	0	0		570		25	0
28	07-Jan-04	08:18	514	40	FALSE	0.14	0	0	0		514		26	0
29	07-Jan-04	08:19	1027	74	FALSE	0.37	0	0	0		1027		27	0
30	07-Jan-04	08:20	544	35	FALSE	0.14	0	0	0		544	3048	28	0
31	07-Jan-04	08:21	0	0	FALSE	0	1	0	1				29	0
32	07-Jan-04	08:22	0	0	FALSE	0	1	1	0				30	0
33	07-Jan-04	08:23	0	0	FALSE	0	1	1	0				31	0
34	07-Jan-04	08:24	0	0	FALSE	0	1	1	0				32	0
35	07-Jan-04	08:25	48	5	FALSE	0.01	0	1	-1	1	48		33	0
36	07-Jan-04	08:26	491	41	FALSE	0.14	0	0	0		491		34	0
37	07-Jan-04	08:27	1046	62	FALSE	0.34	0	0	0		1046		35	0
38	07-Jan-04	08:28	320	27	FALSE	0.08	0	0	0		320		36	0
39	07-Jan-04	08:29	692	46	FALSE	0.2	0	0	0		692		37	0
40	07-Jan-04	08:30	327	32	FALSE	0.08	0	0	0		327		38	0
41	07-Jan-04	08:31	453	40	FALSE	0.12	0	0	0		453		39	0
42	07-Jan-04	08:32	11	2	FALSE	0	0	0	0		11		40	0
43	07-Jan-04	08:33	169	12	FALSE	0.04	0	0	0		169	3557	41	0
Analysis

Cell Formulas
Range	Formula
G3:G43	G3	=IF(A3="","",IF(AND(E3=FALSE,D3>=1),0,IF(AND(E3=FALSE,D3=0),1,"")))
K3:K43	K3	=IF(AND(E3=FALSE,G3=0),C3,"")
L3:L43	L3	=IF(AND(K3<>"",K4=""),SUM(K$2:K4)-SUM(L$2:L2),"")
H4:H43	H4	=IF(G4="","",G3)
I4:I43	I4	=IF(H4="","",G4-H4)
J4:J43	J4	=IF(H4="","",IF(I4<0,ABS(I4),""))
N3:N43	N3	=SUM(IF(FREQUENCY(IF(G$3:G$17282=0,ROW(G$3:G$17282)),IF(G$3:G$17282<>0,ROW(G$3:G$17282)))=M3,1))
Press CTRL+SHIFT+ENTER to enter array formulas.