Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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.

--jaboody

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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. ## 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

```

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

#### 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