Summing consecutive values according to specified time lengths

ian.cook

New Member
Joined
Sep 8, 2012
Messages
9
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
ABCDEFGHIJKLMNO
1Bouts
2DATETIMEVALUESTEPSEXCLACCEL1: =0 Sed; 0: >=1 steps (Act)lagb1breakSTEPSLength (min)FrequencyStep total
307-Jan-0407:5341716FALSE0.10417 1211?????
407-Jan-0407:54170278FALSE0.63000 1702 289
507-Jan-0407:552486100FALSE1.11000 2486 348
607-Jan-0407:562922106FALSE1.38000 2922 442
707-Jan-0407:572896108FALSE1.39000 2896 530
807-Jan-0407:583277106FALSE1.54000 3277 620
907-Jan-0407:592739100FALSE1.22000 2739 715
1007-Jan-0408:00244099FALSE1.08000 2440 810
1107-Jan-0408:0178921FALSE0.19000 789 912
1207-Jan-0408:02117429FALSE0.3000 1174 105
1307-Jan-0408:03177710FALSE0.4000 1777 1110
1407-Jan-0408:0423314FALSE0.05000 233 125
1507-Jan-0408:0515713FALSE0.04000 157 134
1607-Jan-0408:0611411FALSE0.03000 11423123148
1707-Jan-0408:0700FALSE0.03101   156
1807-Jan-0408:0800FALSE0.02110   163
1907-Jan-0408:0900FALSE0.05110   173
2007-Jan-0408:1000FALSE0.04110   181
2107-Jan-0408:1100FALSE0.07110   198
2207-Jan-0408:1200FALSE0.05110   203
2307-Jan-0408:1300FALSE0.02110   210
2407-Jan-0408:144690FALSE0.11110   224
2507-Jan-0408:1526728FALSE0.0701-11267 232
2607-Jan-0408:1612615FALSE0.03000 126 242
2707-Jan-0408:1757037FALSE0.15000 570 251
2807-Jan-0408:1851440FALSE0.14000 514 261
2907-Jan-0408:19102774FALSE0.37000 1027 273
3007-Jan-0408:2054435FALSE0.14000 5443048281
Analysis
Cell Formulas
RangeFormula
G3:G30G3=IF(A3="","",IF(AND(E3=FALSE,D3>=1),0,IF(AND(E3=FALSE,D3=0),1,"")))
K3:K30K3=IF(AND(E3=FALSE,G3=0),C3,"")
L3:L30L3=IF(AND(K3<>"",K4=""),SUM(K$2:K4)-SUM(L$2:L2),"")
H4:H30H4=IF(G4="","",G3)
I4:I30I4=IF(H4="","",G4-H4)
J4:J30J4=IF(H4="","",IF(I4<0,ABS(I4),""))
N3:N30N3=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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 8, 2012
Messages
9
I've added some sample answers:

Step Analysis_DHDSS_long1.2_example_answers.xlsx
ABCDEFGHIJKLMNO
1Bouts
2DATETIMEVALUESTEPSEXCLACCEL1: =0 Sed; 0: >=1 steps (Act)lagb1breakSTEPSLength (min)FrequencyStep total
307-Jan-0407:5341716FALSE0.10417 10
407-Jan-0407:54170278FALSE0.63000 1702 20
507-Jan-0407:552486100FALSE1.11000 2486 30
607-Jan-0407:562922106FALSE1.38000 2922 40
707-Jan-0407:572896108FALSE1.39000 2896 50
807-Jan-0407:583277106FALSE1.54000 3277 613048
907-Jan-0407:592739100FALSE1.22000 2739 70
1007-Jan-0408:00244099FALSE1.08000 2440 80
1107-Jan-0408:0178921FALSE0.19000 789 913557
1207-Jan-0408:02117429FALSE0.3000 1174 100
1307-Jan-0408:03177710FALSE0.4000 1777 110
1407-Jan-0408:0423314FALSE0.05000 233 120
1507-Jan-0408:0515713FALSE0.04000 157 130
1607-Jan-0408:0611411FALSE0.03000 1142312314123123
1707-Jan-0408:0700FALSE0.03101   150
1807-Jan-0408:0800FALSE0.02110   160
1907-Jan-0408:0900FALSE0.05110   170
2007-Jan-0408:1000FALSE0.04110   180
2107-Jan-0408:1100FALSE0.07110   190
2207-Jan-0408:1200FALSE0.05110   200
2307-Jan-0408:1300FALSE0.02110   210
2407-Jan-0408:144690FALSE0.11110   220
2507-Jan-0408:1526728FALSE0.0701-11267 230
2607-Jan-0408:1612615FALSE0.03000 126 240
2707-Jan-0408:1757037FALSE0.15000 570 250
2807-Jan-0408:1851440FALSE0.14000 514 260
2907-Jan-0408:19102774FALSE0.37000 1027 270
3007-Jan-0408:2054435FALSE0.14000 5443048280
3107-Jan-0408:2100FALSE0101   290
3207-Jan-0408:2200FALSE0110   300
3307-Jan-0408:2300FALSE0110   310
3407-Jan-0408:2400FALSE0110   320
3507-Jan-0408:25485FALSE0.0101-1148 330
3607-Jan-0408:2649141FALSE0.14000 491 340
3707-Jan-0408:27104662FALSE0.34000 1046 350
3807-Jan-0408:2832027FALSE0.08000 320 360
3907-Jan-0408:2969246FALSE0.2000 692 370
4007-Jan-0408:3032732FALSE0.08000 327 380
4107-Jan-0408:3145340FALSE0.12000 453 390
4207-Jan-0408:32112FALSE0000 11 400
4307-Jan-0408:3316912FALSE0.04000 1693557410
Analysis
Cell Formulas
RangeFormula
G3:G43G3=IF(A3="","",IF(AND(E3=FALSE,D3>=1),0,IF(AND(E3=FALSE,D3=0),1,"")))
K3:K43K3=IF(AND(E3=FALSE,G3=0),C3,"")
L3:L43L3=IF(AND(K3<>"",K4=""),SUM(K$2:K4)-SUM(L$2:L2),"")
H4:H43H4=IF(G4="","",G3)
I4:I43I4=IF(H4="","",G4-H4)
J4:J43J4=IF(H4="","",IF(I4<0,ABS(I4),""))
N3:N43N3=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,766
Messages
5,542,416
Members
410,550
Latest member
ganeshsamant
Top