Averaging data every 2 cells using VBA

Question

Hi there, I have 24 columns of data, each with 86400 thousands rows of data. I am l looking to average the data so that it is represented by 43200 rows. Therefore, I am looking to go down each column, average 2 cells, then average the next 2, then the next 2, and so on. I am trying to use a macro for this but so far I have been unable to solve the problem. I was wondering if anyone here could help me? It would be much appreciated. Thanks in advance for all your help!!

hansvogelaar · Answer

Rory123908&nbsp;
Try this:
Sub Average2()
    Const fr = 2 ' First data row - change as needed
    Dim ws As Worksheet
    Dim wt As Worksheet
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set wt = Worksheets.Add(After:=ws)
    With wt.Range("A" &amp; fr).Resize(43200, 24)
        .Formula = "=AVERAGE(OFFSET('" &amp; ws.Name &amp; "'!A$" &amp; fr &amp; ",2*(ROW()-" &amp; fr &amp; "),0,2,1))"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

rory123908 · Answer

Hi there, Many thanks for your help. The solution worked! I really appreciate you helping me out. My data is now averaged inside of a new sheet! The data started on row 2 so implementing it was easy! How would I change the code so that I am able to copy the titles/headers from row 1? (these obviously dont need to be averaged or anything like this, just simply copied over). Additionally, There is another column in the sheet that I would also like to copy across to this new sheet. This also does not need any operations done on it, simply copied along side the averaged data. Do you know how I may do this? Thanks again for all your help, it really has been very helpful!

hansvogelaar · Answer

Rory123908&nbsp;
Which column should be copied?
And do you want to copy the value from the even rows or from the odd rows in that column?

rory123908 · Answer

HansVogelaar&nbsp;&nbsp;Hi, to try and explain a bit better, the initial problem is describing output from a tool with various different variables, initially there was 86400 data points (1 per second of a day). These are in columns AL:BI (I have slightly adjusted your code so that it applies to these columns already). The new column I am talking about is in column "i". This represents the time at which the data points were recorded. It has already been sorted so that it is 43200 entries long. I am looking to export this time column alongside the variables which we have averaged in the initial problem. Additionally, I am trying to export them with their headers which currently lie on row 1. I hope this makes sense &amp; thanks again for helping!

hansvogelaar · Answer

Rory123908&nbsp;
See if you can modify this for your purpose:
&nbsp;
Sub Average2()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set wt = Worksheets.Add(After:=ws)
    ' Copy headers in row 1
    wt.Range("B1").Resize(1, 24).Value = ws.Range("AL1").Resize(1, 24).Value
    ' Copy column I to column A
    wt.Range("A1").Resize(43201).Value = ws.Range("I1").Resize(43201).Value
    ' Average data
    With wt.Range("B2").Resize(43200, 24)
        .Formula = "=AVERAGE(OFFSET('" &amp; ws.Name &amp; "'!AL$2,2*(ROW()-2),0,2,1))"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
&nbsp;

Forum Discussion

Averaging data every 2 cells using VBA

6 Replies

Resources