Find the highest rolling average from a range

[Pedro's PhD]

Hi All,

I have numerical data in over 1000 cells in ColumnA, and I am in need of finding the highest average from a rolling average of 10 consecutive cells.

Can anyone help?

Thanks in advance

Pedro

 

[Marcelo Branco]

See if this example with 20 numbers helps

	A​	B​	C​
1​	Numbers​		Top Avg10​
2​	16​		38,3​
3​	50​
4​	39​
5​	8​
6​	25​
7​	34​
8​	22​
9​	30​
10​	50​
11​	25​
12​	80​
13​	70​
14​	16​
15​	4​
16​	27​
17​	12​
18​	21​
19​	45​
20​	35​
21​	39​

Array formula in C2
=MAX(SUBTOTAL(1,OFFSET(A2,ROW(A2:A12)-ROW(A2),0,10)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.