Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate average from every two or three or four rows
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate average from every two or three or four rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate average from every two or three or four rows
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. "OssieMac" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate average from every two or three or four rows
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate average from every two or three or four rows
Hi OssieMac,
Thank you so much for your help. It works perfectly. Turen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate Average | Excel Discussion (Misc queries) | |||
How to calculate an average from various rows??? | Excel Worksheet Functions | |||
How to Calculate Average | Excel Discussion (Misc queries) | |||
CALCULATE WITH AVERAGE ???? | Excel Discussion (Misc queries) | |||
If/Then calculate the average | Excel Worksheet Functions |