Require macro to reduce data by averaging

[dpgillies]

Hello, I am unfamiliar with macros and have a problem that I think a macro may be able to solve.

I have a worksheet with some columns of data:

Time/P1/P2/P3/P4/P5

This data was collected using LabView and a data acquistion system. The problem is the slowest speed that I could collect data was 16 samples per second. I collected about an hour of data each time I ran this test and simply have too much data to go through.

I would like help in creating a macro that will allow me to average a number of rows (16 to 80 or something) and give me the calculated average values in 6 columns next to the 6 columns of raw data. I am simply trying to reduce the amount of data I have to work with and possibly reduce some instrument noise by averaging it out.

If anyone has any idea how to do this (or maybe there's already some function in Excel that will let me do it quickly) let me know.

Thanks,

Dan

[rylo]

Hi

See if this will do

Sub aaa()
  Dim OutSh As Worksheet
  Set OutSh = Sheets("Sheet4")
  rowstoaverage = InputBox("Enter number of rows to average", Default:=16)
  OutSh.Range("A1:F1").Value = Range("A1:F1").Value
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step rowstoaverage
    outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    OutSh.Cells(outrow, 1) = WorksheetFunction.Average(Cells(i, 1).Resize(rowstoaverage, 1))
    OutSh.Cells(outrow, 2) = WorksheetFunction.Average(Cells(i, 2).Resize(rowstoaverage, 1))
    OutSh.Cells(outrow, 3) = WorksheetFunction.Average(Cells(i, 3).Resize(rowstoaverage, 1))
    OutSh.Cells(outrow, 4) = WorksheetFunction.Average(Cells(i, 4).Resize(rowstoaverage, 1))
    OutSh.Cells(outrow, 5) = WorksheetFunction.Average(Cells(i, 5).Resize(rowstoaverage, 1))
    OutSh.Cells(outrow, 6) = WorksheetFunction.Average(Cells(i, 6).Resize(rowstoaverage, 1))
  Next i
  
End Sub

rylo

[dpgillies]

Hi,

That works great!

Thanks!