Hello,
I am not sure if this can be solved in excel or not.
I have 41,000 rows of something like:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 385"]
<tbody>[TR]
[TD="class: xl65, width: 385"]13:23:15,13:23:15,13:23:15,13:24:15, 13:24:24, 14:32:00, 14:34:23, 14:35:00, 14:38:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I want excel to accumulate count as follows:
1) look at the times and add the count every time three minutes pass
So 13:23:15 keeps going at MOST 3 minutes and it accumulates counts. So since less than three minutes passed from 13:23:15 to 13:24:24, we count a 1. Then comes 14:32:00 we want to accumulate the count as long as we are under 3 minutes from the time we had last (14:32:00). So we add another 1 at 14:35:00. So now we have a total of 2. Last number is 14:38:15. So the count adds another 1. So the total is 3. How can we do the accumulated count in excel?
As an example, is this what you mean?
[TABLE="width: 397"]
<tbody>[TR]
[TD="align: right"]Time[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]Count If
< 3 min[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Constant[/TD]
[/TR]
[TR]
[TD="align: right"]
13:23:15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]00:03:00[/TD]
[/TR]
[TR]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]00:00:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]00:00:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]00:00:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13:24:15[/TD]
[TD="align: right"]00:01:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13:24:24[/TD]
[TD="align: right"]00:00:09[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:32:00[/TD]
[TD="align: right"]01:07:36[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:34:23[/TD]
[TD="align: right"]00:02:23[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:35:00[/TD]
[TD="align: right"]00:00:37[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:38:15[/TD]
[TD="align: right"]00:03:15[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In your explanation, you say
13:23:15 to 13:24:24 is the first count... but then the next count is at 14:32:00.
If you're incrementing the counter every time a time appears and it's under 3 minutes since the last count (but presumably greater than zero), wouldn't 13:24:15 be the second count?
Also, wouldn't there be an increment at 14:34:23 because the difference from 14:32:00 to 14:34:23 is only 2m 23sec (less than 3 minutes)?
And wouldn't the last number NOT increment the counter because the difference between 14:35:00 and 14:38:15 is
greater than three minutes?
When you say you have 41,000 rows.. how specifically does that data appear?
Is it one time value per cell and just one column of data?
e.g.
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]13:23:15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13:23:15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13:23:15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13:24:15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13:24:24[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]14:32:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]14:34:23[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]14:35:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]14:38:15[/TD]
[/TR]
</tbody>[/TABLE]
OR
Is it one time value per cell, but with a varying number of cells stretching to the right?
e.g.
[TABLE="width: 891"]
<tbody>[TR]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]13:23:15[/TD]
[TD="align: right"]13:24:15[/TD]
[TD="align: right"]13:24:24[/TD]
[TD="align: right"]14:32:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:33:55[/TD]
[TD="align: right"]14:34:51[/TD]
[TD="align: right"]14:35:44[/TD]
[TD="align: right"]14:38:14[/TD]
[TD="align: right"]14:38:15[/TD]
[TD="align: right"]14:39:57[/TD]
[TD="align: right"]14:41:22[/TD]
[TD="align: right"]14:43:02[/TD]
[TD="align: right"]14:44:21[/TD]
[/TR]
[TR]
[TD="align: right"]14:44:43[/TD]
[TD="align: right"]14:44:53[/TD]
[TD="align: right"]14:47:10[/TD]
[TD="align: right"]14:49:15[/TD]
[TD="align: right"]14:50:04[/TD]
[TD="align: right"]14:52:57[/TD]
[TD="align: right"]14:55:15[/TD]
[TD="align: right"]14:57:27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15:00:08[/TD]
[TD="align: right"]15:02:03[/TD]
[TD="align: right"]15:03:00[/TD]
[TD="align: right"]15:04:23[/TD]
[TD="align: right"]15:06:06[/TD]
[TD="align: right"]15:06:23[/TD]
[TD="align: right"]15:06:35[/TD]
[TD="align: right"]15:08:35[/TD]
[TD="align: right"]15:10:49[/TD]
[/TR]
[TR]
[TD="align: right"]15:12:07[/TD]
[TD="align: right"]15:12:46[/TD]
[TD="align: right"]15:13:28[/TD]
[TD="align: right"]15:15:27[/TD]
[TD="align: right"]15:17:56[/TD]
[TD="align: right"]15:18:11[/TD]
[TD="align: right"]15:19:07[/TD]
[TD="align: right"]15:19:54[/TD]
[TD="align: right"]15:20:58[/TD]
[/TR]
[TR]
[TD="align: right"]15:21:08[/TD]
[TD="align: right"]15:24:04[/TD]
[TD="align: right"]15:25:08[/TD]
[TD="align: right"]15:26:52[/TD]
[TD="align: right"]15:28:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15:29:05[/TD]
[TD="align: right"]15:31:11[/TD]
[TD="align: right"]15:32:37[/TD]
[TD="align: right"]15:32:56[/TD]
[TD="align: right"]15:33:11[/TD]
[TD="align: right"]15:35:16[/TD]
[TD="align: right"]15:37:07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15:39:33[/TD]
[TD="align: right"]15:41:51[/TD]
[TD="align: right"]15:43:37[/TD]
[TD="align: right"]15:44:13[/TD]
[TD="align: right"]15:44:40[/TD]
[TD="align: right"]15:46:29[/TD]
[TD="align: right"]15:47:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15:49:14[/TD]
[TD="align: right"]15:49:25[/TD]
[TD="align: right"]15:51:07[/TD]
[TD="align: right"]15:51:48[/TD]
[TD="align: right"]15:53:49[/TD]
[TD="align: right"]15:55:21[/TD]
[TD="align: right"]15:57:48[/TD]
[TD="align: right"]15:59:37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15:59:51[/TD]
[TD="align: right"]16:00:21[/TD]
[TD="align: right"]16:00:38[/TD]
[TD="align: right"]16:00:51[/TD]
[TD="align: right"]16:03:38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16:03:53[/TD]
[TD="align: right"]16:04:54[/TD]
[TD="align: right"]16:05:38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
OR
Is it literally 41,000 rows of data where you've a single column, and in each cell is a set of comma separated time values?
[TABLE="width: 448"]
<tbody>[TR]
[TD]13:23:15,13:23:15,13:23:15,13:24:15,13:24:24,14:32:00[/TD]
[/TR]
[TR]
[TD]14:32:18,14:34:05,14:35:41,14:36:38,14:38:51,14:40:00,14:42:06,14:44:15[/TD]
[/TR]
[TR]
[TD]14:48:29,14:51:15,14:53:41,14:55:27,14:57:50,14:59:13,14:59:50,15:00:43[/TD]
[/TR]
[TR]
[TD]15:01:23,15:03:38,15:06:02,15:08:14,15:11:04,15:12:02,15:14:53,15:17:43[/TD]
[/TR]
[TR]
[TD]15:20:28,15:21:17,15:22:52,15:24:45,15:25:20,15:25:26,15:25:44,15:27:56[/TD]
[/TR]
[TR]
[TD]15:31:06,15:33:49,15:35:44,15:37:08,15:37:42[/TD]
[/TR]
[TR]
[TD]15:38:11,15:38:57,15:41:14,15:43:47,15:45:04,15:45:34,15:48:27[/TD]
[/TR]
[TR]
[TD]15:50:13,15:52:07,15:54:33,15:54:43,15:55:18,15:57:45,16:00:07[/TD]
[/TR]
[TR]
[TD]16:00:24,16:01:06,16:02:50,16:03:21,16:04:29,16:06:05,16:06:10,16:06:28[/TD]
[/TR]
[TR]
[TD]16:06:58,16:09:49,16:10:00,16:10:10,16:12:49[/TD]
[/TR]
[TR]
[TD]16:13:16,16:14:33,16:17:14[/TD]
[/TR]
</tbody>[/TABLE]
It really doesn't matter which format the data is in, the approach might be slightly different depending on which structure it's in.
If it's the last format, a UDF (User Defined Function) could be created, or simply a macro that does the calculation.
Alternatively, you could split the data out into the format above using TEXT TO COLUMNS and splitting out on the COMMA as the delimiter.
Finally, the formula. As I'm not 100% on the calculation you mentioned, I'm not able to give a formula.
If the initial example I used is correct, here's the formulae I used:
[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD="align: center"]
A[/TD]
[TD="align: center"]
B[/TD]
[TD="align: center"]
C[/TD]
[TD="align: center"]
D[/TD]
[TD="align: center"]
E[/TD]
[TD="align: center"]
F[/TD]
[/TR]
[TR]
[TD="align: center"]
2[/TD]
[TD="align: center"]
Time[/TD]
[TD="align: center"]
Difference[/TD]
[TD="align: center"]
Count IF < 3 min[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
Constant[/TD]
[/TR]
[TR]
[TD="align: center"]
3[/TD]
[TD="align: center"]
13:23:15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]00:03:00[/TD]
[/TR]
[TR]
[TD="align: center"]
4[/TD]
[TD="align: center"]13:23:15[/TD]
[TD="align: center"]=B4-B3[/TD]
[TD="align: center"]=IF(AND(C4>0,C4<$F$3),D3+1,D3)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
5[/TD]
[TD="align: center"]13:23:15[/TD]
[TD="align: center"]=B5-B4[/TD]
[TD="align: center"]=IF(AND(C5>0,C5<$$F$3),D4+1,D4)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
6[/TD]
[TD="align: center"]13:23:15[/TD]
[TD="align: center"]=B6-B5[/TD]
[TD="align: center"]=IF(AND(C6>0,C6<$F$3),D5+1,D5)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
7[/TD]
[TD="align: center"]13:24:15[/TD]
[TD="align: center"]=B7-B6[/TD]
[TD="align: center"]=IF(AND(C7>0,C7<$F$3),D6+1,D6)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
8[/TD]
[TD="align: center"]13:24:24[/TD]
[TD="align: center"]=B8-B7[/TD]
[TD="align: center"]=IF(AND(C8>0,C8<$F$3),D7+1,D7)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
9[/TD]
[TD="align: center"]14:32:00[/TD]
[TD="align: center"]=B9-B8[/TD]
[TD="align: center"]=IF(AND(C9>0,C9<$F$3),D8+1,D8)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
10[/TD]
[TD="align: center"]14:34:23[/TD]
[TD="align: center"]=B10-B9[/TD]
[TD="align: center"]=IF(AND(C10>0,C10<$F$3),D9+1,D9)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
11[/TD]
[TD="align: center"]14:35:00[/TD]
[TD="align: center"]=B11-B10[/TD]
[TD="align: center"]=IF(AND(C11>0,C11<$F$3),D10+1,D10)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
12[/TD]
[TD="align: center"]14:38:15[/TD]
[TD="align: center"]=B12-B11[/TD]
[TD="align: center"]=IF(AND(C12>0,C12<$F$3),D11+1,D11)[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Of course, the formula can be changed to:
=IF(AND(B4-B3>0,B4-B3<$F$3),D2+1,D3)
removing the need for column C (which was there just to make working out the count visually easier!)
Finally, B3 needs to have the same value as B4 as a starting position.
If I've not correctly understood how you derive your count, could you give an example showing the data and the count increment alongside!
