Forum Discussion

Rory123908's avatar
Rory123908
Copper Contributor
Nov 30, 2021

Averaging data every 2 cells using VBA

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...
excel
Formulas and Functions
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Nov 30, 2021

Rory123908 

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" & fr).Resize(43200, 24)
        .Formula = "=AVERAGE(OFFSET('" & ws.Name & "'!A$" & fr & ",2*(ROW()-" & fr & "),0,2,1))"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
Rory123908's avatar
Rory123908
Copper Contributor
Nov 30, 2021
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's avatar
    HansVogelaar
    MVP
    Nov 30, 2021

    Rory123908 

    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's avatar
      Rory123908
      Copper Contributor
      Nov 30, 2021

      HansVogelaar 

       

      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 & thanks again for helping!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 30, 2021

        Rory123908 

        See if you can modify this for your purpose:

         

        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('" & ws.Name & "'!AL$2,2*(ROW()-2),0,2,1))"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

         

Resources