Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#3
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
|
|||
|
|||
Quote:
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? |
#6
|
|||
|
|||
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. Quote:
Last edited by Kevin@Radstock : January 23rd 13 at 07:38 AM |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
|
|||
|
|||
Quote:
I appreciate your help!!! |
#9
|
|||
|
|||
Hi comp1
No problem. |
#10
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 month rolling totals | Excel Programming | |||
Rolling 12 month average | Excel Worksheet Functions | |||
Rolling 12 month total | Excel Worksheet Functions | |||
12 month Rolling Total | Excel Worksheet Functions | |||
How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) |