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
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You could put this in B4 (or any column, row 4) and drag down

    =IF(MOD(ROW($A4),4)=0,AVERAGE($A1:$A4),"")


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

    Code:
    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
  •