Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Average Many Small Fixed Intervals In A Large Column Of Data

  1. #1
    Join Date
    15th September 2006
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

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

    For macro code, try
    VB:
    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 
    
    

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Moving Average Of Real Time Data At Timed Intervals
    By Gunshot in forum Excel General
    Replies: 2
    Last Post: February 20th, 2014, 16:12
  2. Macro: Small & Large Formula
    By Demeter in forum Excel General
    Replies: 6
    Last Post: December 7th, 2006, 20:36
  3. Large and Small
    By ZeroMan001 in forum Excel General
    Replies: 1
    Last Post: March 30th, 2006, 12:01
  4. display of data with fixed intervals from inputbox
    By difre in forum Excel General
    Replies: 5
    Last Post: October 7th, 2005, 21:31
  5. large combination from small set
    By Justin Doward in forum Excel General
    Replies: 3
    Last Post: February 18th, 2005, 21:17

Bookmarks

Posting Permissions

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