calculate average from every two or three or four rows

[Guest]

The data are in one column with variable number of rows. How can I write a
macro to calculate average from every two or three consecutive rows?

For example, see the following conversion:

Data Average every 2 rows average every 3 rows
1 2 3
3 6 9
5 10 15
7 14 21
9 18
11 22
13
15
17
19
21
23

Thank you very much for your time.

 

[Guest]

Hi Turen,

Try this. I assumed that the source data is in column A I have set the
column number for the results to match the group to be averaged. That is if
averaging 2 cells then results in column 2, averaging 3 cells results in
column 3 etc. but you can edit this to place results wherever you like.

I assumed that you only need the principle for the programming and that you
can edit it from there to get as many groups of averages as you want but if
you want help to do more with it then feel free to get back to me.

Sub Averages()
Dim ws1 As Worksheet
Dim rng1 As Range
Dim i As Long
Dim aveGroup As Long
Dim colNbr As Long 'Column for results

Set ws1 = Sheets("Sheet1")
'Assign range to a variable
With ws1
Set rng1 = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

'Alternative methods of assigning range to a variable
'Set rng1 = ws1.Range("A2:A13")
'Set rng1 = Sheets("Sheet1").Range("A2:A13")

'set avegroup to the number of rows to average
aveGroup = 3

'Set colNbr for column to place results
colNbr = aveGroup

With rng1
For i = 1 To .Rows.Count Step aveGroup
ws1.Cells(Rows.Count, colNbr).End(xlUp).Offset(1, 0) _
= WorksheetFunction.Average(.Cells(i, 1), _
.Cells(i + aveGroup - 1, 1))
Next i
End With

End Sub


Regards,

OssieMac

 

[Guest]

OssieMac,

Thank you very much for the tips. I am very new to the excel programming.
So this is still beyond my current level. Could you modify your code so it
is executable using the sample data I provided? You can just calculate the
average for every two cells and put the results in the column B. Assume data
is in Column A. Thanks a lot.

 

[Guest]

Hi again Turen,

Irrespective of how may values are to be averaged, the logic for the
programming is the same. I originally tested it with the data you supplied.
However, I have modified it so that there is now an InputBox so that when you
run the macro you can enter the number of values to be averaged and the
results will always be in column B. Any exisiting values in column B will be
deleted and replaced with the new values each time you run the macro.

Set up your Sheet1 with Data in column A as per the sample data you
originally posted and insert a column header in cell B1 called 'Averages' (or
whatever name you want to use.)

Sub Averages()
Dim ws1 As Worksheet
Dim rng1 As Range
Dim i As Long
Dim aveGroup As Variant
Dim colNbr As Long 'Column for results

aveGroup = InputBox _
("Enter the number of cells to average" _
& Chr(13) & "Cancel to exit.")

If aveGroup = "" Then
MsgBox "User cancelled - Processing aborted"
End
End If

Set ws1 = Sheets("Sheet1")

'Assign range to a variable
With ws1
Set rng1 = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

'Set colNbr for column to place results
colNbr = 2

'Clear existing data from column 2
ws1.Range(Cells(2, colNbr), _
Cells(Rows.Count, colNbr).End(xlUp)).Clear

With rng1
For i = 1 To .Rows.Count Step aveGroup
ws1.Cells(Rows.Count, colNbr).End(xlUp).Offset(1, 0) _
= WorksheetFunction.Average(.Cells(i, 1), _
.Cells(i + aveGroup - 1, 1))
Next i
End With

End Sub


Regards,

OssieMac

 

[Guest]

Hi OssieMac,

Thank you so much for your help. It works perfectly.

Turen