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

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

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

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

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

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

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

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

INDEED IT IS.

Many, many thanks.