Results 1 to 2 of 2

Thread: Help with averaging problem

  1. #1

    Help with averaging problem

    Register for a FREE account, and/
    or Log in to avoid these ads!

    We are trying to write a write a macro that will take the average of every four numbers like A1-A4 then A5-A8 is there a function we can utilize to help us analize this mountain of data we have that we could easily apply down the column?

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    British Columbia
    Excel Version
    Excel 2010
    You could put this in B4 (or any column, row 4) and drag down


    Or you could put this macro into the worksheet's module. It puts answers in column C

    Sub AvEvery4Rows()
        Dim rng As Range    'range to work with
        Dim cel As Range    'individual cells within that range
        Dim Tally As Double 'total of the 4 rows being averaged
        Dim i As Integer    'incrementer, will be 0 to 1 less than number of rows
        Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        For Each cel In rng
            If cel.Row Mod 4 = 0 Then
                For i = 0 To 3
                    Tally = Tally + cel.Offset(-i, 0).Value
                Next i
                cel.Offset(0, 2).Value = Tally / 4
                i = 0
                Tally = 0
            End If
        Next cel
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts