MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Chris Rock on April 26, 2001 8:20 AM

I have data that is updated weekly, and I've created a column that tracks a running average. I have been changing the formula frequently to adjust which columns are included in the average calculation.

For example, Column IV, Row 2 has the Average Calculation. Today, the average was for cells S2:AB2, or ten weeks worth of data. Next week, I'll want to change that average formula to T2:AC2, because I'll be adding one more week's worth of data.

Is there any way to automatically get my average formula to average the last ten (or however many) weeks worth of data? Any combinations of IF statements? A macro?

I may be out of luck, I understand.

Row 1 contains the date.
Rows 2 through 49 contain data (volume per half hour).
Each worksheet in the workbook represents a day of the week.
Column IV (the last column in the sheet) contains the averages.


Posted by Mark W. on April 26, 2001 8:34 AM

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.

Posted by Mark W. on April 26, 2001 8:42 AM

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!

Posted by Chris Rock on April 26, 2001 8:48 AM

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.

Posted by Chris Rock on April 26, 2001 9:23 AM

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.

Posted by Mark W. on April 26, 2001 9:39 AM

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))

Posted by Mark W. on April 26, 2001 9:46 AM

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))

Posted by Chris Rock on April 26, 2001 12:26 PM

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.

Posted by Tim Francis-Wright on April 26, 2001 12:38 PM

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

Posted by Chris Rock on April 26, 2001 12:43 PM

Re: How it works...


INDEED IT IS.

Many, many thanks.