Announcement

Collapse
No announcement yet.

Average Many Small Fixed Intervals In A Large Column Of Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Average Many Small Fixed Intervals In A Large Column Of Data

    Hi Everyone,

    I am pretty green with regards to VBA and programming in general, so please forgive me for my ignorance if this question is stupid or has already been answered.

    I would like to write a macro that would take the average of many small intervals in a column like A1:A60, A61:A120, A121:A180, etc. all the way through a column of data. It would be nice if the calculated averages could be displayed sequentially like B1, B2, B3, etc. It would also be helpful if the user was asked specifically what range to consider for interval averaging.

    Thank you in advance for any help you might be able to provide me.

    --jaboody

  • #2
    Re: Average Many Small Fixed Intervals In A Large Column Of Data

    Hi jaboody,

    Welcome to OzGrid!!

    In B1 and copied down,

    =AVERAGE(INDEX($A$1:$A$1000,ROWS($A$1:$A1)*$C$1-$C$1+1):INDEX($A$1:$A$1000,ROWS($A$1:$A1)*$C$1))

    where C1 houses the range to average.

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Average Many Small Fixed Intervals In A Large Column Of Data

      For macro code, try
      Code:
      Sub AverageBits()
      Dim rRange As Range
      Dim rAv As Range
      Dim lLoop As Long
      Dim lCellRow As Long
      
          Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
      
              For lLoop = 1 To rRange.Rows.Count Step 60
                  lCellRow = lCellRow + 1
                  Set rAv = Range(rRange.Cells(lLoop, 1), rRange.Cells(lLoop + 60, 1))
                  Cells(lCellRow, 2) = WorksheetFunction.Average(rAv)
              Next lLoop
      
      End Sub

      Comment

      Working...
      X