Getting average of variable number of rows

[abberyfarm]

Hi there,

I have time in Col A and values in Col B. I'm trying to get the average every 15 minutes but the number of row in each 15 minute block is variable. Would anybody know a way in which I can get the average of the values and place it in Col C?

Many thanks

?

Excel Workbook
	A	B	C
1	Time	Value	Average
2	01/09/2014 00:00:23	2
3	01/09/2014 00:00:23	3
4	01/09/2014 00:03:13	5
5	01/09/2014 00:06:01	5
6	01/09/2014 00:08:07	3
7	01/09/2014 00:10:02	7
8	01/09/2014 00:10:02	2
9	01/09/2014 00:10:57	3
10	01/09/2014 00:13:42	8
11	01/09/2014 00:14:52	9
12	01/09/2014 00:14:52	0	4.272727
13	01/09/2014 00:15:46	5
14	01/09/2014 00:15:46	6
15	01/09/2014 00:17:37	5
16	01/09/2014 00:18:35	8
17	01/09/2014 00:19:41	3
18	01/09/2014 00:20:31	7
19	01/09/2014 00:21:30	5
20	01/09/2014 00:23:22	6
21	01/09/2014 00:24:18	8
22	01/09/2014 00:25:29	0
23	01/09/2014 00:27:15	7
24	01/09/2014 00:28:17	2
25	01/09/2014 00:29:06	3	5
26	01/09/2014 00:30:04
Sheet1

 

[WarPigl3t]

I hope you know how to insert vba code into your macros.

lastRow = Range("A" & Rows.Count).End(xlup).Row
output = FALSE
i = 2
Do Until i > lastRow
     If output = FALSE Then
          firstOutput = "B" & i
          maxTime = Range("A" & i).value + Format("00:15", "hh:mm")
          output = TRUE
     End If
     If Range("A" & i).value > maxTime Then
          i = i - 1
          Range("C" & i).value = "=AVERAGE(" & firstOutput & ":B" & i & ")"
          output = FALSE
     End If
     i = i + 1
Loop
i = i - 1
Range("C" & i).value = "=AVERAGE(" & firstOutput & ":B" & i & ")"

 

[bschwartz]

If you can add a helper column, you can use this formula. The helper column can also be hidden:

Excel 2010
	A	B	C	D
1	Time	Value	15th inc.	Average
2	1/9/2014 0:00	2	0
3	1/9/2014 0:00	3	0
4	1/9/2014 0:03	5	0
5	1/9/2014 0:06	5	0
6	1/9/2014 0:08	3	0
7	1/9/2014 0:10	7	0
8	1/9/2014 0:10	2	0
9	1/9/2014 0:10	3	0
10	1/9/2014 0:13	8	0
11	1/9/2014 0:14	9	0
12	1/9/2014 0:14	0	0	4.272727
13	1/9/2014 0:15	5	1
14	1/9/2014 0:15	6	1
15	1/9/2014 0:17	5	1
16	1/9/2014 0:18	8	1
17	1/9/2014 0:19	3	1
18	1/9/2014 0:20	7	1
19	1/9/2014 0:21	5	1
20	1/9/2014 0:23	6	1
21	1/9/2014 0:24	8	1
22	1/9/2014 0:25	0	1
23	1/9/2014 0:27	7	1
24	1/9/2014 0:28	2	1
25	1/9/2014 0:29	3	1	5
26	1/9/2014 0:30		2
Sheet3

Cell Formulas
Range	Formula
C2	=ROUNDDOWN(MINUTE(A2)/15,0)
D2	=IF(C3>C2,AVERAGEIF($C$2:C2,C2,$B$2:B2),"")

 

[abberyfarm]

Hi warpiglet,

Thanks for your help with this.

I'm getting a type mismatch error on the following line?

[FONT=arial]maxTime = Range("A" & i).value + Format("00:15", "hh:mm")[/FONT]

Many thanks