Selecting Various Ranges

[akennedy]

I want to be able to select a various range from one column and
average the numbers. Then, paste the average to a different cell.

For example:
Select cells F4 thru F12, then average
Paste average value in cell J4
Select cells F13 thru F21, then average
Paste average value in cel J5

Always the same # of cells to select, and always putting the average
in the next cell in the "J" column. I need to do this for about 50
iterations. Can anyone offer some assistance?

Thanks.

 

[Guest]

Hi,

Maybe something like this:

Sub test()
Dim rng As Range
Dim rngTarget As Range
Dim iCellsCount As Integer
Dim i As Integer

'starting cell to be averaged
Set rng = Range("F4")
'number of cells to include in average
iCellsCount = 9
'starting cell for output
Set rngTarget = Range("J4")

For i = 1 To 50
If WorksheetFunction.Sum(rng.Resize(iCellsCount)) > 0 Then
rngTarget.Value =
WorksheetFunction.Average(rng.Resize(iCellsCount))
Else
rngTarget.Value = 0
End If
Set rngTarget = rngTarget.Offset(1)
Set rng = rng.Offset(iCellsCount)
Next i


End Sub


You can also do it without VBA. For example, enter this formula in J4 and
copy down as needed.

=IF(SUM(OFFSET($F$4,(ROW()-4)*9, 0, 9, 1))>0,
AVERAGE(OFFSET($F$4,(ROW()-4)*9,0,9,1)), 0)