A "running average" that averages only the last 8 entries?

For demonstration puposes let's say that cells
A2:K2 contains {10,20,30,40,50,60,70,80,90,100,110}.
Enter the following array formula into cell M2 (this
would be like your IV2):

{=AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$L$2)),,-9,1,10))}

This averages the last ten values in row 2.

Correction...

Keep forgetting that the AVERAGE() function itself
will recognize an array as an argument, and doesn't
need to be entered as an array formula. Simply,
=AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$L$2)),,-9,1,10))
will do!

Re: Correction...

Thanks, I believe this will do it. I'll apply it to my data, and I think I'll get just what I need.

I really appreciate it.

Re: Correction...

Mark W. -
Can you explain this formula in english?
I get the AVERAGE part, and the COUNT parts, but I am a little confused when it comes to the OFFSET and INDEX parts.

When I apply this formula to my data, I get a different result than when I manually write an AVERAGE formula that covers the last 10 "weeks" worth of data.

Basically, I've got far more than 10 weeks of data, 1 week per column, and I'd like the running average to only take the last (latest) 10 weeks.

How it works...

Chris, the arguments to the INDEX() function are:

=INDEX(array,row_num,column_num)

The array is row 2:2. The row_num is left blank
to indicate all (in this case, 1) rows. I used
the COUNT() function to determine the last column
with a value. The INDEX function returns a reference
to this nth column. OFFSET() takes this reference
and "goes back" 9 columns to get the the 1st of the
10 last values and then returns a reference consisting
of 1 row and 10 columns (the last 2 arguments to the
OFFSET() function).

So, if you have 10 weeks of daily data perhaps your
formula should look like...

=AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$IU$2)),,-69,1,70))

Re: How it works...

Just noticed your "1 week per column" comment. This
should do the trick:

=AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$IU$2)),,-9,1,10))

Re: How it works...

I am having trouble with the Offset portion of this function. Using your explaination (Thank you!!) of the different parts of the formula, I broke it up into 4 parts. When I tried the "offset" part, I get a #VALUE error. This makes sense, because it looks like the OFFSET function doesn't have a numerical answer. However, when I use the AVERAGE function with it, my answer is incorrect . . . I get a different answer when I use a simple AVERAGE function.

How can I prove that the OFFSET function is doing what it is supposed to? I am using some test data now, based on your first example. I have data in row 2, in the first 11 columns. In the 13th column, I have the formula, which I am using to average the "last" 10 numbers. The numbers are 10 through 120, increasing by 10 each time. An AVERAGE of the "last" 10 numbers (20 through 120) shows 70. Using the long formula you supplied, - =AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$M$2)),,-9,1,10)) - I get 75.

Any ideas?
Thanks a ton for your help so far. If I figure it out, I will post the results.

Re: How it works...

An AVERAGE of the "last" 10 numbers (20 through 120) shows 70. Using the long formula you supplied, - =AVERAGE(OFFSET(INDEX(2:2,,COUNT($A$2:$M$2)),,-9,1,10)) - I get 75.

The last 10 numbers are actually 30 through 120;
the range of 20 through 120 has 11 numbers.
If tyou add 20 through 120 and divide by 11,
you get 70.

Mark's formula is correct: it averages 30 through
120 by adding them and dividing by 10.

HTH

Re: How it works...

INDEED IT IS.

Many, many thanks.