Average groups of numbers within a larger group

smileytmroper

New Member
I have a large group of numbers in a column. I would like to average those numbers in groups of 7. So average the first 7, average the 2nd 7, and so on down the whole list. Is there a way to do this without manually selecting each group to find their average?
I have Kutools and ASAP utilities if there is an option there also.
Thanks
 

mikerickson

MrExcel MVP
If your numbers are in column A
=AVERAGE(INDEX(A:A, 1, 1):INDEX(A:A, 7, 1)) will return the average of the first 7 rows
=AVERAGE(INDEX(A:A, 8, 1):INDEX(A:A, 14, 1)) is the average of the next 7

If you put this in a cell
=AVERAGE(INDEX(A:A, 7*(ROWS($1:1)-1)+1, 1):INDEX(A:A, (7*ROWS($1:1))+1, 1)) and drag down, it will return all of the 7 cell averages.
 

Yongle

Well-known Member
perhaps ...

Excel 2016 (Windows) 32 bit
A
B
C
1
Numbers ResultFormula in A2 copied down the whole column
2
10​
11​
=IF(MOD(ROW(),7)=2,AVERAGE(A2:A8),"")
3
15​
4
20​
5
12​
6
5​
7
6​
8
9​
9
22​
24​
10
24​
11
35​
12
45​
13
16​
14
13​
15
13​
16
12​
18​
17
14​
18
16​
19
18​
20
20​
21
22​
22
24​
Sheet: Sheet3
 

Some videos you may like

This Week's Hot Topics

Top