VBA to Calculate Median/Average of 134,000 Rows (Separated by Hundredths of a Second)

[BarGraph]

Hi All,

I have a spreadsheet with 134,000 rows. Each row contains data from a hundredth of a second (1343 seconds total).
Each hundredth of a second has several points of data that was recorded. What I would like to do is, summarize the data by second. (Average & Median of each Data 1, Data 2, Data 3 for each second.)

Example of Data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[/TR]
[TR]
[TD].001[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD].002[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD].003[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

I have already set a floor.
Ex:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Floor[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[/TR]
[TR]
[TD].001[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD].002[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD].003[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

Ideally, it would look something like this when done:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Floor[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[TD][/TD]
[TD]Floor[/TD]
[TD]Data 1 Aver.[/TD]
[TD]Data 2 Aver.[/TD]
[TD]Data Three Aver.[/TD]
[/TR]
[TR]
[TD].001[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2.666[/TD]
[TD]3[/TD]
[TD]3.66[/TD]
[/TR]
[TR]
[TD].002[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD].003[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've tried writing the following code:

Private Sub cmdRun_Click()
'Declare variables
Dim x As String
Dim row As Integer
Dim lastSecondInFloor
Dim time As String
Dim floor As String
Dim newFloor As String
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String


'Get floor
x = 0
row = 3
floor = Cells(row, 2)
Do
floor = Cells(row, 2)
If floor <> x Then
x = x + 1
'The commented out line below seemed to work for inserting a message into the last line in a "floor"
'I just don't know how to make it take the median/average of whole floor
'Cells(row - 1, 10) = "last"
lastSecondInFloor = row - 1
'This code below didn't work as expected
Cells(row - 1, 10) = WorksheetFunction.Median("lastSecondInFloor,8:row,8")


End If


row = row + 1
'last second number to be found goes here
Loop While x <= 1343




End Sub

Let me know what you guys think. I am stumped on how to get VBA to take each floor "chunk" and do the calculation. To make things worse, not every second of data contains the same number of datapoints (eg. sometimes there would be multiple seconds repeated) so I can't break the data up every hundred rows.

Thanks for reading!

 

[steve the fish]

Your results suggest you are using mean not median. For that reason id definately be using a pivot table for this. Nothing else will compete for speed over that many rows.

 

[BarGraph]

Hi Steve,

Thank you for your response! I would like to find mean and also median as well for each second, I just listed mean in my example to keep my message as short as possible. Sorry for the confusion!

I did initially try using a pivot table, but was unable to get it work correctly.
I assigned the Floors to be rows and attempted to group them, but the only way to get it to group appeared to be by increments. But each Floor contains differing amounts of datarows.

Any idea how to group correctly?

 

[steve the fish]

You can get your floor column using roundup formula. However median is as far as I'm aware not possible with a pivot. Mean is ok. You then need to put floor in the rows part of the pivot and your data1 data2 data3 in the totals part. Click them one by one and change them to average.