Rolling 12 month

comp1

unread,

Jan 13, 2013, 10:06:05 PM1/13/13

to

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!

--
comp1

MyVeryOwnSelf

unread,

Jan 15, 2013, 6:43:16 PM1/15/13

to

> 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

unread,

Jan 17, 2013, 9:45:31 PM1/17/13

to

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!

--
comp1

MyVeryOwnSelf

unread,

Jan 18, 2013, 6:41:11 PM1/18/13

to

> 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

unread,

Jan 21, 2013, 10:18:29 PM1/21/13

to

'MyVeryOwnSelf[_3_ Wrote:
> ;1608717']> 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?

--
comp1

Kevin@Radstock

unread,

Jan 22, 2013, 2:44:16 AM1/22/13

to

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

comp1;1608789 Wrote:
> 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?

--

Kevin@Radstock

MyVeryOwnSelf

unread,

Jan 22, 2013, 3:00:49 PM1/22/13

to

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

comp1

unread,

Jan 23, 2013, 9:31:08 PM1/23/13

to

Kevin@Radstock;1608792 Wrote:
> 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.

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

--
comp1

Kevin@Radstock

unread,

Jan 24, 2013, 4:20:05 AM1/24/13

to

Hi comp1

No problem.

comp1;1608838 Wrote:
> Thank you so much! The 2nd option is working well for me.
> I appreciate your help!!!

--

Kevin@Radstock

comp1

unread,

Jan 26, 2013, 1:59:21 PM1/26/13

to

Kevin@Radstock;1608849 Wrote:
> 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?

--
comp1