# Getting average of variable number of rows

#### abberyfarm

##### Well-known Member
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
ABC
1TimeValueAverage
201/09/2014 00:00:232
301/09/2014 00:00:233
401/09/2014 00:03:135
501/09/2014 00:06:015
601/09/2014 00:08:073
701/09/2014 00:10:027
801/09/2014 00:10:022
901/09/2014 00:10:573
1001/09/2014 00:13:428
1101/09/2014 00:14:529
1201/09/2014 00:14:5204.272727
1301/09/2014 00:15:465
1401/09/2014 00:15:466
1501/09/2014 00:17:375
1601/09/2014 00:18:358
1701/09/2014 00:19:413
1801/09/2014 00:20:317
1901/09/2014 00:21:305
2001/09/2014 00:23:226
2101/09/2014 00:24:188
2201/09/2014 00:25:290
2301/09/2014 00:27:157
2401/09/2014 00:28:172
2501/09/2014 00:29:0635
2601/09/2014 00:30:04
Sheet1

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### WarPigl3t

##### Well-known Member
I hope you know how to insert vba code into your macros.
Code:
``````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

##### Well-known Member
If you can add a helper column, you can use this formula. The helper column can also be hidden:

Excel 2010
ABCD
1TimeValue15th inc.Average
21/9/2014 0:0020
31/9/2014 0:0030
41/9/2014 0:0350
51/9/2014 0:0650
61/9/2014 0:0830
71/9/2014 0:1070
81/9/2014 0:1020
91/9/2014 0:1030
101/9/2014 0:1380
111/9/2014 0:1490
121/9/2014 0:14004.272727
131/9/2014 0:1551
141/9/2014 0:1561
151/9/2014 0:1751
161/9/2014 0:1881
171/9/2014 0:1931
181/9/2014 0:2071
191/9/2014 0:2151
201/9/2014 0:2361
211/9/2014 0:2481
221/9/2014 0:2501
231/9/2014 0:2771
241/9/2014 0:2821
251/9/2014 0:29315
261/9/2014 0:302
Sheet3
Cell Formulas
RangeFormula
C2=ROUNDDOWN(MINUTE(A2)/15,0)
D2=IF(C3>C2,AVERAGEIF(\$C\$2:C2,C2,\$B\$2:B2),"")

#### abberyfarm

##### Well-known Member
Hi warpiglet,

Thanks for your help with this.

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

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

Many thanks

Replies
2
Views
480
Replies
13
Views
822
Replies
6
Views
250
Replies
2
Views
1K
Replies
3
Views
588

1,196,027
Messages
6,012,950
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back