[challenging?] Arrays etc

[o]

Hi, I am quite new to excel and have been trying to make a formula
to calculate a simple thing, yet it seems complicated.

I have a column filled with values, I want to know the maximum
value of a function applied to a range of values.
For exemple I want the maximum value of a moving average

exemple
I have

0
3
4
5
6
7
1
3

I could calculate a moving average ( windows size = 3 ) like this

0
3
4 7/3 ( = (0+3+4)/3 )
5 4 ( = (4+5+6)/3 )
6 5 ...
7 6
1 14/3
3 11/3

just by typing in the formula in the first cell and extending it
then I could calculate the maximum of the second column which is 6.

However, I would like a formula that gives 6 directly
so it would look like

= MAX( ........... )

where ........... is an array...

I tought about MAX( AVERAGE(OFFSET(RANGE ... smthg )))
but I don't see how to indicate iteration in the offset.

Any help appreciated.

 

[Frank Kabel]

Hi
for the range A1:A10 try the following formula (does not
take blank or text cells into account):
=SUMPRODUCT(MAX((A1:A8+A2:A9+A3:A10)))/3

 

[Frank Kabel]

Hi
for the range A1:A10 try
=SUMPRODUCT(MAX((A1:A8+A2:A9+A3:A10)))/3

Note: does not take blank or text cells into account

 

[irrelevent]

Hi
for the range A1:A10 try
=SUMPRODUCT(MAX((A1:A8+A2:A9+A3:A10)))/3

Note: does not take blank or text cells into account

This won't work for my problem. AVERAGE was just an example,
could be STDEV...


I tried to fill column A with the numbers
0
1
2
3
4
5
6
7
etc

and wrote MAX( AVERAGE( OFFSET(B1:B3,A1:A7,0) ) )
I was hoping offset would return an array of arrays
( namely {B1:B3,B2:B4,B3:B5...} ), that applying average
would yield {AVERAGE(B1:B3),AVERAGE(B2:B4) ... } and that MAX
would take the max of it all...
Well guess what, it doesn't work :-/

Any ideas ?