rolling 12 months

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" <[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee )



"Max" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> "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<at>tarkon.ee )


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

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" <[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee )



"Max" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> "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<at>tarkon.ee )


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