rolling 12 months
 

I would like to set up my spreadsheet to give me a running 12 month total, by
dropping the oldest data and using my current data. i.e. january thru dec
totals then in next jan the previous year would drop off when I input current
jan totals.

Thanks again for your help

D

One way ..

Assume the set-up below, months in col A, from row2 down,
the values in col B, & the rolling 12 month total to be in B1

Mth-Yr <R12m-Total
Aug-04 50
Sep-04 80
Oct-04 70
Nov-04 10
Dec-04 50
Jan-05 90
Feb-05 20
Mar-05 90
Apr-05 40
May-05 40
Jun-05 70
Jul-05 40
....

Put in B1:

=SUM(OFFSET($A$1,MATCH(OFFSET($A$1,COUNTA(A:A)-1,0),A:A,0)-1,1,-12))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"D" wrote in message
...
I would like to set up my spreadsheet to give me a running 12 month total,
by
dropping the oldest data and using my current data. i.e. january thru
dec
totals then in next jan the previous year would drop off when I input
current
jan totals.

Thanks again for your help

D

Hi

This assumes there is a single row for every moth, and always a row for a
month, does it? More general solution (dates in column A, values in column
B, headers in row 1, sum is placed outside of data range, p.e. in cell C2)
=SUMIF(A:A,""&DATE(YEAR(MAX(A:A)),MONTH(MAX(A:A))-11,0),B:B)


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Max" wrote in message
...
One way ..

Assume the set-up below, months in col A, from row2 down,
the values in col B, & the rolling 12 month total to be in B1

Mth-Yr <R12m-Total
Aug-04 50
Sep-04 80
Oct-04 70
Nov-04 10
Dec-04 50
Jan-05 90
Feb-05 20
Mar-05 90
Apr-05 40
May-05 40
Jun-05 70
Jul-05 40
...

Put in B1:

=SUM(OFFSET($A$1,MATCH(OFFSET($A$1,COUNTA(A:A)-1,0),A:A,0)-1,1,-12))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"D" wrote in message
...
I would like to set up my spreadsheet to give me a running 12 month
total,
by
dropping the oldest data and using my current data. i.e. january thru
dec
totals then in next jan the previous year would drop off when I input
current
jan totals.

Thanks again for your help

D

"Arvi Laanemets" wrote:
This assumes there is a single row for every moth,
and always a row for a month, does it?

Yes of course <g, the assumed set up was described
in the response, as always ..

More general solution (dates in column A, values in column
B, headers in row 1, sum is placed outside of data range, p.e. in cell C2)
=SUMIF(A:A,""&DATE(YEAR(MAX(A:A)),MONTH(MAX(A:A))-11,0),B:B)

Just a thought. The above would return zero if the dates were not "real"
dates.
(A possibility that I had guessed might be the case, in the earlier
response. It wasn't clear from the orig. post.)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi


"Max" wrote in message
...
"Arvi Laanemets" wrote:
This assumes there is a single row for every moth,
and always a row for a month, does it?

Yes of course <g, the assumed set up was described
in the response, as always ..

More general solution (dates in column A, values in column
B, headers in row 1, sum is placed outside of data range, p.e. in cell
C2)
=SUMIF(A:A,""&DATE(YEAR(MAX(A:A)),MONTH(MAX(A:A))-11,0),B:B)

Just a thought. The above would return zero if the dates were not "real"
dates.
(A possibility that I had guessed might be the case, in the earlier
response. It wasn't clear from the orig. post.)

P.e. month names as text?
It's simply - then OP will be in trouble :-)

(Of-course even then it's possible, but the formula will be huge. It's main
reason I always advice to use real dates - when there is a need, then you
always can format them to be displayed as month names. But my 1st preference
will be the format "yyyy.mm", which allows to sort data properly.)


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--