Test data reduction macro

**a1aexcel** · 02-18-2013, 11:54 AM

Hi all,

I have a large amount of test data that I need to process into useable information. Because of my hardware, the test sample rate was over 1000Hertz. Anyway,I need to reduce the length of the columns of test data by averaging them every 17 rows. I attached a sample of the data I am working with, some of the data is over 100,000 rows

. I cannot figure out how to begin averaging the data and increment the range until the end of the column. Please help....
BTW, I am using excel2010, if that matters.

test book1.xlsx

**mike7952** · 02-18-2013, 12:43 PM

Give this a try,

Option Explicit

Sub abc()
 Dim r As Long, lr As Long, c As Long, lc As Long
 Dim a, i As Long, ii As Long
 
 With Worksheets("sheet1")
    lr = .Cells(Rows.Count, "a").End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    ReDim a(1 To lr, 1 To lc / 2 + 1)
    i = 1
    For c = 1 To lc
        If Len(Trim$(.Cells(10, c))) > 0 Then
            ii = ii + 1
            a(i, ii) = .Cells(10, c)
        End If
    Next
    
    For r = 11 To lr Step 18
        i = i + 1
        a(i, 1) = .Cells(r, 1)
        ii = 2
        For c = 2 To lc Step 2
            a(i, ii) = WorksheetFunction.Average(.Range(.Cells(r, c).Address, .Cells(r + 17, c).Address))
            ii = ii + 1
        Next
    Next
 End With
 With Worksheets.Add
    With .Cells(1).Resize(i, UBound(a, 2))
        .NumberFormat = "0.00000000"
        .Value = a
    End With
    .Cells.EntireColumn.AutoFit
 End With
End Sub

**AlphaFrog** · 02-18-2013, 12:58 PM

I had a go at it as well.

Your description said a sample size of 17 but your example workbook had a sample size of 18.

Change the sample size in the code to suit.

Sub Subsample_Data()
    
    Dim vData, vSample, SampleSize&, i&, j&, k&
    Dim Avgs(1 To 18) As Single
    
    SampleSize = 18
    
    vData = Range("A11", Range("R" & Rows.Count).End(xlUp))
    ReDim vSample(1 To UBound(vData, 1), 1 To UBound(vData, 2))
    
    For i = 1 To UBound(vData, 1)
    
        For k = 2 To 18 Step 2
            Avgs(k) = Avgs(k) + vData(i, k)
        Next k
        
        If i Mod SampleSize = 0 Then
            j = j + 1
            vSample(j, 1) = vData(i - SampleSize + 1, 1)
            For k = 2 To 18 Step 2
                vSample(j, k) = Avgs(k) / SampleSize
            Next k
            Erase Avgs
        End If
        
    Next i
    
    Range("V10").Resize(, 18).Value = Range("A10").Resize(, 18).Value 'Headers
    Range("V11").Resize(j, 18).Value = vSample                    'Data
    
End Sub

**a1aexcel** · 02-18-2013, 02:30 PM

AlphaFrog,

I got a "Type Mismatch" runtime error with your code on the line beginning "Avgs(k) =". No idea why....
If you can explain, it would help me understand.

Mike7952's code does what I need to do, my job is to understand how.

Thanks a lot, both of you!

**AlphaFrog** · 02-18-2013, 02:52 PM

Originally Posted by a1aexcel

AlphaFrog,

I got a "Type Mismatch" runtime error with your code on the line beginning "Avgs(k) =". No idea why....
If you can explain, it would help me understand.

It worked for me given your example data. Are you using the same worksheet?

Type mismatch in this case may mean the data it is trying to add is text and not numeric.

**a1aexcel** · 02-18-2013, 03:03 PM

I did use a different sheet.... went back and used a new fresh sheet with raw data which is what my example was, and it worked, with 22,000 data points. Your code was fine, I am apparently the weak link.

**thomas.r** · 04-18-2013, 02:33 PM

Originally Posted by a1aexcel

Hi all,

I have a large amount of test data that I need to process into useable information. Because of my hardware, the test sample rate was over 1000Hertz. Anyway,I need to reduce the length of the columns of test data by averaging them every 17 rows. I attached a sample of the data I am working with, some of the data is over 100,000 rows

. I cannot figure out how to begin averaging the data and increment the range until the end of the column. Please help....
BTW, I am using excel2010, if that matters.

Attachment 214942

I will like to use alaexcel test book1.xlsx as an example. I would like to reduce the columns averaging them every 5 rows in reference to the middle number for example rows 1+2+4+5 divided by row 3. I will then have to compare the average of the sumed rows to row 3. if row 3 is greater than the sum of rows 1+2+4+5 then flag it and increment by one. So the next rows will be the average of 2+3+5+6 then this time compare it to row 4 if greater than 8 flag it and repeat the process any help please.

**arlu1201** · 04-18-2013, 02:50 PM

Thomas,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Test data reduction macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Re: Test data reduction macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions