Summing moving ranges & dates
 

I have data laid out as shown below:

Jan-04 Feb-04 --------- --------- --------- Jan-05 Feb-05
6,942.0 6,721.0 --------- --------- --------- 7,605.0 7,171.0

What I'm trying to do is have Excel calculate the 13 Month average for each
row of data - for example, if the current month is Jan-05 then the desired
result would be to sum the values from Jan-05 through Jan-04 and divide by
13. The "Month-Yr" column labels are formatted exactly as they appear above.

Can someone help solve this? Any assistance provided would be greatly
appreciated. Thanks.

Try something like:

AVERAGE(M2:OFFSET(M2,0,-12))

where M2 contains the data for the last month in the target period.

--

Vasant


"Spartacus" wrote in message
...
I have data laid out as shown below:

Jan-04 Feb-04 --------- --------- --------- Jan-05 Feb-05
6,942.0 6,721.0 --------- --------- --------- 7,605.0 7,171.0

What I'm trying to do is have Excel calculate the 13 Month average for
each
row of data - for example, if the current month is Jan-05 then the desired
result would be to sum the values from Jan-05 through Jan-04 and divide by
13. The "Month-Yr" column labels are formatted exactly as they appear
above.

Can someone help solve this? Any assistance provided would be greatly
appreciated. Thanks.

Vasant - your suggestion worked beautifully, however, due to the fact that
I'd be applying this to a large number of cells in several worksheets I'd
like to somehow be able to automate the "M2" reference to pickup the data
associated with whatever the current month is.

Thanks in advance for your input and suggestions.


"Vasant Nanavati" wrote:

Try something like:

AVERAGE(M2:OFFSET(M2,0,-12))

where M2 contains the data for the last month in the target period.

--

Vasant


"Spartacus" wrote in message
...
I have data laid out as shown below:

Jan-04 Feb-04 --------- --------- --------- Jan-05 Feb-05
6,942.0 6,721.0 --------- --------- --------- 7,605.0 7,171.0

What I'm trying to do is have Excel calculate the 13 Month average for
each
row of data - for example, if the current month is Jan-05 then the desired
result would be to sum the values from Jan-05 through Jan-04 and divide by
13. The "Month-Yr" column labels are formatted exactly as they appear
above.

Can someone help solve this? Any assistance provided would be greatly
appreciated. Thanks.

How would you define the "data associated with the current month?"

Computers are dumb; you have to give them pretty precise instructions. Is it
the second row in the column headed with the month immediately past? What is
the format (text or date)? Etc.

--

Vasant


"Spartacus" wrote in message
...
Vasant - your suggestion worked beautifully, however, due to the fact that
I'd be applying this to a large number of cells in several worksheets I'd
like to somehow be able to automate the "M2" reference to pickup the data
associated with whatever the current month is.

Thanks in advance for your input and suggestions.


"Vasant Nanavati" wrote:

Try something like:

AVERAGE(M2:OFFSET(M2,0,-12))

where M2 contains the data for the last month in the target period.

--

Vasant


"Spartacus" wrote in message
...
I have data laid out as shown below:

Jan-04 Feb-04 --------- --------- --------- Jan-05 Feb-05
6,942.0 6,721.0 --------- --------- --------- 7,605.0 7,171.0

What I'm trying to do is have Excel calculate the 13 Month average for
each
row of data - for example, if the current month is Jan-05 then the
desired
result would be to sum the values from Jan-05 through Jan-04 and divide
by
13. The "Month-Yr" column labels are formatted exactly as they appear
above.

Can someone help solve this? Any assistance provided would be greatly
appreciated. Thanks.