Average groups of numbers within a larger group

[smileytmroper]

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]

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]

perhaps ...

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Numbers[/td][td] Result[/td][td]Formula in A2 copied down the whole column[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]

11​

[/td][td]=IF(MOD(ROW(),7)=2,AVERAGE(A2:A8),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#E2EFDA]

15​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#E2EFDA]

20​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#E2EFDA]

12​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td=bgcolor:#E2EFDA]

9​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td=bgcolor:#DDEBF7]

22​

[/td][td=bgcolor:#DDEBF7]

24​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td=bgcolor:#DDEBF7]

24​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td=bgcolor:#DDEBF7]

35​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td=bgcolor:#DDEBF7]

45​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td=bgcolor:#DDEBF7]

16​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td=bgcolor:#DDEBF7]

13​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td=bgcolor:#DDEBF7]

13​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td=bgcolor:#FFF2CC]

12​

[/td][td]

18​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td=bgcolor:#FFF2CC]

14​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td=bgcolor:#FFF2CC]

16​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td=bgcolor:#FFF2CC]

18​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td=bgcolor:#FFF2CC]

20​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td=bgcolor:#FFF2CC]

22​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td=bgcolor:#FFF2CC]

24​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]