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
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

       

      • Rory123908's avatar
        Rory123908
        Copper Contributor
        Dec 02, 2021
        Many thanks for all of your help. This solved the issue perfectly. All the best, and thank you for helping.

Resources