Rolling year calculations

[louiscourtney]

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

 

[Mike H]

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option

Mike

 

[louiscourtney]

Mike that looks perfect, the only problem i have
I have each year on a differant tab what part of the sum should i change to
pick up the other tab?

Thanks for your help so far
Shane

 

[Toppers]

How are your tabs named ? (And I assume data is Jan to Dec on each)

 

[Toppers]

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A100"),"<="
&TODAY(),INDIRECT("'"&$H$1:$H$2&"'!B1:B100"))-SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A100"),"<=" &TODAY()-365,INDIRECT("'"&$H$1:$H$2&"'!B1:B100")))

Put Mike's 06 data on Sheet1 anf 07 on Sheet2. Put "Sheet1" in H1 (no
quotes) and "Sheet2" in H2.

(It then needs adapting for your sheet names)

HTH

 

[louiscourtney]

Thanks Toppers
I should have made myself clearer from the start
I will try to explain

One tab named history with names down one side in column A and then the
months going across the top Jan, Feb,mar,Apr,May etc etc with a totals column
at the end
Then there is the current year tab with the same names down one side and the
months across the top.
What i need to know is the calculation that links both sheets giving me a
riolling 12 month figure

Thanks to anyone that can solve this for me

 

[Mike H]

Hi,

I thought I'd grasped the data layout but the wheels came off again. In your
Tab called history you probably have some (history that is). When this year
becomes history; and I wish it would, where will you put the data if all you
have in your history tab are a matrix of names/months with no reference to
year?

Mike

 

[Toppers]

Try:


Months are assumed in B1 to M1 on both sheets and B2:M2 is data for person
in A2

Put in your current w/sheet in (say) S2 and copy down

=SUMPRODUCT(SUMIF(INDIRECT("'"&$Z$1:$Z$2&"'!B1:M1"),"<="
&TODAY(),INDIRECT("'"&$Z$1:$Z$2&"'!B" & ROW() & ":M"
&ROW()))-SUMIF(INDIRECT("'"&$Z$1:$Z$2&"'!B1:M1"),"<="
&TODAY()-365,INDIRECT("'"&$Z$1:$Z$2&"'!B" & ROW() & ":M" & ROW())))

In Z1="History"
Z2=Current Tab name

 

[louiscourtney]

Mike
I've changed the spread sheet to inculde all the data on one tab which now
looks something like this

Column A = Names
Column B = years
Column C to N = months
Column O = year total

A4 = Shane
B4 = 2007
B5 = 2006

Then there is various numbers on each month and each year

Then i would like in cell P4 is the rolling figure for the 12 months

I hope i have made myself clearer

Shane

 

[Toppers]

try:

in P4

=SUMPRODUCT(--(MONTH($C$1:$N$1)<=MONTH(TODAY())),$C4:$N4)+SUMPRODUCT(--(MONTH($C$1:$N$1)>MONTH(TODAY())),$C5:$N5)

Months in C1 to N1 must be defined as a DATE e.g. 01/01/07, 01/02/07 (i
have used dd/mm/yy)...the Year does NOT matter ... and just format cells as
CUSTOM => "mmm"

HTH