Forum Discussion
Averaging data every 2 cells using VBA
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- 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!
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?
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!
