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

BarGraph

New Member
Joined
May 11, 2018
Messages
2
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:
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top