Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 -- |
#5
|
|||
|
|||
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
Rolling Year in Excel | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) | |||
rolling months | Excel Discussion (Misc queries) |