I have some formulas that average the last 12 months of data. As new data
comes in the formula changes to reflect the change in the range. This all
works. The question is - am I doing it the "Best" way ? I figure out what
column I need to start with and what column I need to end with and I build a
string for the range then use the Indirect function. As I said, it works,
but I'm wondering if there is a simpler approach. As an additional twist,
besides the last 12 months I do the last 6 months and the last 3 months and
the same formula works for all 3 - by just pointing to a cell which contains
the number of months. Thanks for any help on this.

The easiest way to average a moving range is to stop it from moving.

Rather than inserting new data on the right-hand side of the worksheet,
insert a new blank column in column A, pushing the old data to the right.
Then insert the latest data in column A.

=AVERAGE(A1:M1) won't need constant adjustment.
--
Gary''s Student - gsnu200846


"dhstein" wrote:

I have some formulas that average the last 12 months of data. As new data
comes in the formula changes to reflect the change in the range. This all
works. The question is - am I doing it the "Best" way ? I figure out what
column I need to start with and what column I need to end with and I build a
string for the range then use the Indirect function. As I said, it works,
but I'm wondering if there is a simpler approach. As an additional twist,
besides the last 12 months I do the last 6 months and the last 3 months and
the same formula works for all 3 - by just pointing to a cell which contains
the number of months. Thanks for any help on this.

On the one hand, that's a clever idea. On the other hand, this is an
existing workbook that I'm modifying for a friend's business. He's used to
seeing data from left to right with the most recent month followed by the
averages. So I won't be able to change the layout, and as I've said, what I
have is working and I'm just looking for another, perhaps better way of doing
the averages. But thanks for the response.

David

"Gary''s Student" wrote:

The easiest way to average a moving range is to stop it from moving.

Rather than inserting new data on the right-hand side of the worksheet,
insert a new blank column in column A, pushing the old data to the right.
Then insert the latest data in column A.

=AVERAGE(A1:M1) won't need constant adjustment.
--
Gary''s Student - gsnu200846


"dhstein" wrote:

I have some formulas that average the last 12 months of data. As new data
comes in the formula changes to reflect the change in the range. This all
works. The question is - am I doing it the "Best" way ? I figure out what
column I need to start with and what column I need to end with and I build a
string for the range then use the Indirect function. As I said, it works,
but I'm wondering if there is a simpler approach. As an additional twist,
besides the last 12 months I do the last 6 months and the last 3 months and
the same formula works for all 3 - by just pointing to a cell which contains
the number of months. Thanks for any help on this.

Is there *always* 12 numbers to average?

Assuming the data is in a contiguous range:

=AVERAGE(OFFSET(A1,,COUNT(A1:Z1)-1,,-12))

--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...
On the one hand, that's a clever idea. On the other hand, this is an
existing workbook that I'm modifying for a friend's business. He's used
to
seeing data from left to right with the most recent month followed by the
averages. So I won't be able to change the layout, and as I've said, what
I
have is working and I'm just looking for another, perhaps better way of
doing
the averages. But thanks for the response.

David

"Gary''s Student" wrote:

The easiest way to average a moving range is to stop it from moving.

Rather than inserting new data on the right-hand side of the worksheet,
insert a new blank column in column A, pushing the old data to the right.
Then insert the latest data in column A.

=AVERAGE(A1:M1) won't need constant adjustment.
--
Gary''s Student - gsnu200846


"dhstein" wrote:

I have some formulas that average the last 12 months of data. As new
data
comes in the formula changes to reflect the change in the range. This
all
works. The question is - am I doing it the "Best" way ? I figure out
what
column I need to start with and what column I need to end with and I
build a
string for the range then use the Indirect function. As I said, it
works,
but I'm wondering if there is a simpler approach. As an additional
twist,
besides the last 12 months I do the last 6 months and the last 3 months
and
the same formula works for all 3 - by just pointing to a cell which
contains
the number of months. Thanks for any help on this.

Thanks for the reply. The first cell is not always "A1". The data starts in
D5 so at one time, 12 months was D5:O5. The next month the 12 months was
E5:P5, then F5:Q5 etc.

"T. Valko" wrote:

Is there *always* 12 numbers to average?

Assuming the data is in a contiguous range:

=AVERAGE(OFFSET(A1,,COUNT(A1:Z1)-1,,-12))

--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...
On the one hand, that's a clever idea. On the other hand, this is an
existing workbook that I'm modifying for a friend's business. He's used
to
seeing data from left to right with the most recent month followed by the
averages. So I won't be able to change the layout, and as I've said, what
I
have is working and I'm just looking for another, perhaps better way of
doing
the averages. But thanks for the response.

David

"Gary''s Student" wrote:

The easiest way to average a moving range is to stop it from moving.

Rather than inserting new data on the right-hand side of the worksheet,
insert a new blank column in column A, pushing the old data to the right.
Then insert the latest data in column A.

=AVERAGE(A1:M1) won't need constant adjustment.
--
Gary''s Student - gsnu200846


"dhstein" wrote:

I have some formulas that average the last 12 months of data. As new
data
comes in the formula changes to reflect the change in the range. This
all
works. The question is - am I doing it the "Best" way ? I figure out
what
column I need to start with and what column I need to end with and I
build a
string for the range then use the Indirect function. As I said, it
works,
but I'm wondering if there is a simpler approach. As an additional
twist,
besides the last 12 months I do the last 6 months and the last 3 months
and
the same formula works for all 3 - by just pointing to a cell which
contains
the number of months. Thanks for any help on this.

Ok, just change the "anchor cell" :

=AVERAGE(OFFSET(D5,,COUNT(D5:?5)-1,,-12))

Where ? is the last column of the range. ? should be big enough to allow for
future data entry. For example:

=AVERAGE(OFFSET(D5,,COUNT(D5:AX5)-1,,-12))

That gives you a total of 47 cells.


--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...
Thanks for the reply. The first cell is not always "A1". The data starts
in
D5 so at one time, 12 months was D5:O5. The next month the 12 months was
E5:P5, then F5:Q5 etc.

"T. Valko" wrote:

Is there *always* 12 numbers to average?

Assuming the data is in a contiguous range:

=AVERAGE(OFFSET(A1,,COUNT(A1:Z1)-1,,-12))

--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...
On the one hand, that's a clever idea. On the other hand, this is an
existing workbook that I'm modifying for a friend's business. He's
used
to
seeing data from left to right with the most recent month followed by
the
averages. So I won't be able to change the layout, and as I've said,
what
I
have is working and I'm just looking for another, perhaps better way of
doing
the averages. But thanks for the response.

David

"Gary''s Student" wrote:

The easiest way to average a moving range is to stop it from moving.

Rather than inserting new data on the right-hand side of the
worksheet,
insert a new blank column in column A, pushing the old data to the
right.
Then insert the latest data in column A.

=AVERAGE(A1:M1) won't need constant adjustment.
--
Gary''s Student - gsnu200846


"dhstein" wrote:

I have some formulas that average the last 12 months of data. As
new
data
comes in the formula changes to reflect the change in the range.
This
all
works. The question is - am I doing it the "Best" way ? I figure
out
what
column I need to start with and what column I need to end with and I
build a
string for the range then use the Indirect function. As I said, it
works,
but I'm wondering if there is a simpler approach. As an additional
twist,
besides the last 12 months I do the last 6 months and the last 3
months
and
the same formula works for all 3 - by just pointing to a cell which
contains
the number of months. Thanks for any help on this.