Rolling 12 month

[comp1]

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 no
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

 

[MyVeryOwnSelf]

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))

 

[comp1]

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 fo
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 an
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
wanted to included a rolling 12month total so Jan 2014 it will calculat
a total from Feb 2013-Jan2014.

Thanks for the help

 

[MyVeryOwnSelf]

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?

 

[comp1]

;1608717']> It seems so close but its still not working.

The AL14-37 is where the "total" points for that month will be kept s

wanted to included a rolling 12month total so Jan 2014 it wil calculat
a total from Feb 2013-Jan2014.

If I understand the requirement, the formul
=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 th
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 colum
0.
0.
2.
0.
0.
0.
0.
0.
0.
0.
2.
2.

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 the
have to be cleared for it to work

 

[Kevin@Radstock]

Hi

have a look at these three ways and adapt as needed.
1/ =SUM(OFFSET($AL$14,COUNTA(AL14:AL100)-1,0,-12,1))
2/ =SUM(OFFSET($AL$14,MATCH(MAX(A14:A100),A14:A100,0),0,-12,1))
3/ =SUM(OFFSET($AL$14,MATCH(DATE(2014,6,1),A14:A100,0),0,-12,1)), chang
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 mm
yyyy.

 

[MyVeryOwnSelf]

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. Thenthe numbers displayed will match the numbers in the computation. Otherwisethere might be extra "invisible" trailing digits in the totals column.

 

[comp1]

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)), chang
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 mm
yyyy.

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

 

[Kevin@Radstock]

Hi comp1

No problem.

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

 

[comp1]

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 the
have points in the rolling total column they would like to take on
point away for good behavior but if there are NO points they receiv
nothing.
One point is ONLY removed IF the employee has perfect attendance and I
they have a number in their rolling points column.

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