Expert: Subbu - 4/22/2009

Question
QUESTION: I have time series data collected every second for 1 day. I need 10 second averages.

The time (11:43:00, 11:43:01, 11:43:02...) are in column A and the data I need averaged are in column B (20, 34, 25...)

I am trying to write a macro which will help me with data analysis.I have my data stored in Excel. I have about 86400 data points (for each day and I need to do this for about 30 days.) I know how to get the averages by typing in =average(b2:b12).. but I do not want to have to do this 8000 times in each sheet. I do not want running averages, just the average of column B every 10 seconds. Is there another way to do this in Excel with a macro?

ANSWER: Hi molly,

As you said you have 86400 data points.  One quick question, are these data points stored in two separate worksheets(like in sheet1 have 40000 and sheet2 have 46400) ?  

In Excel worksheet it is not possible to store more than 65536 rows.

Do you want these averages in the same worksheet or in different worksheet?

Reply me as soon as possible I will give you the code.

Subbu.

---------- FOLLOW-UP ----------

QUESTION: Hi,
Thank you for your response.  
I have many files that are all in .csv form. I can pull the sections of a sheet that I need to analyze into excel. Typically I need about 2-3 hours of data from each day. I would like the 10 second averages in one sheet and I want to be able to select the start time and the end time. Ideally the new averaged data will have time in column A, and 10 second averages of the data in column B (C, D....) (The data are from a project where we have ambient air samples for an entire day and then specific emission samples mixed in, I only need the 10 second averages for the emission samples. 11:43:00 to 12:53:00 for example).

Thank you again.
Monica

Answer
Hi Monica,

I am assuming your data is like this:

Time         Data    Average
11:43:01   15
11:43:02   12
11:43:03   14
11:43:04   13
11:43:05   11
11:43:06   54
11:43:07   15
11:43:08   12
11:43:09   14
11:43:10   13
11:43:11   11
11:43:12   54
11:43:13   15
11:43:14   12
11:43:15   14
11:43:16   13
11:43:17   11
11:43:18   54
11:43:19   42
11:43:20   23

I just created a sample excel file and assumed Column A as Time and Column B as Data and Column C as Average.  Here is my code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/22/2009 by tatas
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim iTotRec As Integer
Dim r1 As Integer, r2 As Integer
Dim i As Integer
Dim avg As Integer

iTotRec = InputBox("Enter Last Row Number")

r1 = 1
r2 = 2
avg = 0
For i = 2 To iTotRec
   Range(Trim(Trim("B") & Trim(Str(i)))).Select
   avg = avg + Range(Trim(Trim("B") & Trim(Str(i)))).Value
   If r1 Mod 10 = 0 Then
       Range(Trim(Trim("C") & Trim(Str(r2)))).Select
       Range(Trim(Trim("C") & Trim(Str(r2)))).Value = avg / 10
       avg = 0
       r2 = r2 + 1
   End If
   r1 = r1 + 1
Next i
End Sub


Once you execute above macro, it will ask you enter last row number(you can give just row number, in this example I gave 21) then you will get averages in Column C (Average).

I took 10 rows for calculating average.

Output:
Time         Data   Average
11:43:01   15   17.3
11:43:02   12   24.9
11:43:03   14   
11:43:04   13   
11:43:05   11   
11:43:06   54   
11:43:07   15   
11:43:08   12   
11:43:09   14   
11:43:10   13   
11:43:11   11   
11:43:12   54   
11:43:13   15   
11:43:14   12   
11:43:15   14   
11:43:16   13
11:43:17   11
11:43:18   54
11:43:19   42
11:43:20   23


If this is not correct then give me sample data and output how you need and I can update my code and send you.

Subbu.