CODEWITHSUNDEEP
vbaexcel
Rahul
Rahul

Reputation: 5

How do I calculate the average of different ranges in the same column in Excel preferably using command button?

I'm using Excel 2013. There are around fifty worksheets and I've to calculate hundreds of averages in a worksheet. The data is spread like C3:C10, C12:C16, C18:C35, etc. and I want to calculate the average at the end of each of these ranges like C11, C17, C36, etc. The range always lie within the same column and each range is separated by two blank cells but the range is not fixed.

I tried recording a macro.

Sub Averaging() 
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-12]C:R[-1]C)"
End Sub

But I don't know how to make R[-12]C part dynamic. I was thinking of creating a command button which after selecting the cell, for example the range is C3:C10 and I select cell C11, and upon pressing the command button it will automatically pick the range and calculate the average. Is it possible?

Any help will be appreciated. VBA, formulas anything.

Upvotes: 0

Views: 4597

Answers (2)

user3598756
user3598756

Reputation: 29421

you could use

Sub WriteAverage(columnRng As Range)
Dim iArea As Long

With columnRng.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers).Areas
    For iArea = 1 To .Count
        With .Item(iArea).Cells
            .Offset(.Rows.Count).Resize(1).FormulaR1C1 = "=average(R" & .Cells(1, 1).Row & "C:R[-1]C)"
        End With
    Next iArea
End With

End Sub

to be called like follows

Call WriteAverage(Columns("C"))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Click on the first cell with data in column C and run this macro:

Sub dural()
    Dim r1 As Range, r2 As Range
    Set r1 = ActiveCell
    Do
        Set r2 = r1.End(xlDown)
        If r2.Row = Rows.Count Then Exit Sub
        r2.Offset(1, 0).Formula = "=AVERAGE(" & Range(r1, r2).Address(0, 0) & ")"
        Set r1 = r2.Offset(3, 0)
    Loop
End Sub

Before:

enter image description here

and after:

enter image description here

Upvotes: 1

Related Questions