Rolling 12 month
 

Hi,
Im trying to figure out a formula for a rolling 12 month.

Exp: Jan 2013-Dec 2013
(at the begin of Jan-14 it would count 12 months back)
Feb 2013-Jan 2014

In my spreadsheet I have years 2013 and 2014.

Column A14:A37 have Months/Year (Jan-13)
Column AL14:AL37 have the totals I need.

My formula right now is:=SUM(OFFSET(AL14,MAX(0,COUNT(AL14:AL37)-24),0,12,1))

It doesnt seem to be calculating correctly.

Any help would be appreciated!

Rolling 12 month
 

Im trying to figure out a formula for a rolling 12 month.

Column AL14:AL37 have the totals I need.

My formula right now
is:=SUM(OFFSET(AL14,MAX(0,COUNT(AL14:AL37)-24),0,12,1))

Could this be it?
=SUM(OFFSET(AL14,MAX(0,COUNT(AL14:AL37)-12),0,12,1))

It seems so close but its still not working.

It will not let me attach the spreadsheet.

In my column A its Jan 2013 - Dec 2014

Its two years that just need to keep rolling a 12month total for attendance.

The columns with the formulas for attendance will remain the same.
Someone will just be updating the month/year column.

5 Letters all equaling some kind of point for each day they are out and it will be recorded in the correct month and day.

The AL14-37 is where the "total" points for that month will be kept so I wanted to included a rolling 12month total so Jan 2014 it will calculate a total from Feb 2013-Jan2014.

Thanks for the help!

Rolling 12 month
 

It seems so close but its still not working.

The AL14-37 is where the "total" points for that month will be kept so I
wanted to included a rolling 12month total so Jan 2014 it will calculate
a total from Feb 2013-Jan2014.

If I understand the requirement, the formula
=SUM(OFFSET(AL14,MAX(0,COUNT(AL14:AL37)-12),0,12,1))
seems to work.

For example, with AL14:A26 containing 1,2,3,...,13, and the rest of the column empty, the formula returns 90, which equals SUM(AL15:AL26).

If that's wrong, what should the result be in this case?

Am I off by one someplace?

Dec 2013- Nov 2014 (just to see if it grabs the last 12 months)

Total column
0.5
0.0
2.0
0.0
0.0
0.0
0.0
0.0
0.5
0.5
2.0
2.0

Total should be 7.5 but when I use the formula it comes back 7.0.

The previous months still contain numbers in the total column. Do they have to be cleared for it to work?

Hi

have a look at these two ways and adapt as needed.
1/ =SUM(OFFSET($AL$14,COUNTA(AL14:AL100)-1,0,-12,1))
2/ =SUM(OFFSET($AL$14,MATCH(DATE(2014,6,1),A14:A100,0 ),0,-12,1)), change the date as required to sum the previous 12 months from that date.
Note: The dates in A14:A37 are the 1st of each month & formatted as mmm yyyy.

Rolling 12 month
 

Total column

0.5
0.0
2.0
0.0
0.0
0.0
0.0
0.0
0.5
0.5
2.0
2.0

I get 7.5 plugging your data into my spreadsheet.

Previous months are not cleared.

I suspect the difference is rounding. In the formulas that calculate the totals, try rounding the result to the number of decimal places desired. Then the numbers displayed will match the numbers in the computation. Otherwise there might be extra "invisible" trailing digits in the totals column.

Thank you so much! The 2nd option is working well for me.
I appreciate your help!!!

Hi comp1

No problem.

Here is a little twist they put into it.

Point system, meaning if the employee has 0 pts for the month and they have points in the rolling total column they would like to take one point away for good behavior but if there are NO points they receive nothing.
One point is ONLY removed IF the employee has perfect attendance and IF they have a number in their rolling points column.

I was thinking of adding another column for Perfect Attendance?
Ideas?