Rolling average calculation (WTD)

I'm stuck and need a little help.

I am trying to create a formula that calculates an average over a rolling 119 day period

The problem that I have, is that I need it to ignore certain days.

I see two ways to achieve this but cant seem to get it right.


1, should a cell have a value of (A/L) or (sick) I need the calculation to ignore these days from the reference period.

2, For every (A/L) or (sick) it should extend the ref period by the same number.....for example : should a member of staff take 7 A/L days the ref period extends to 126 days


any clues ?

Might be barking up the wrong tree but if you're saying each daily sum should reference the last 119 valid days (be they blank/hours) then perhaps:

Please Login or Register  to view this content.

Hi Fredy_1972,

So you need to add all the hours worked in the last 119 days that were worked. You toss out all weekends and Sick and A/L days. I had this great formula to count all the A/L plus Sick days in the last 119 days and make the range start further up your range to calculate it correctly. Then it occured to me what if the extended range backwards included some Sick or A/L days. I'd need to count them too. Recursion...

I'd suggest this solution: Sort the Date and Hours (together) using a Z->A sort by Date. Then filter to only show Hours that are greater than Zero. Then Sum the Hours down 119 rows and this is your total hours worked 119 days where hours were worked.

Does this process (not formula) solve your question?

Originally Posted by MarvinP

I had this great formula to count all the A/L plus Sick days in the last 119 days and make the range start further up your range to calculate it correctly. Then it occured to me what if the extended range backwards included some Sick or A/L days. I'd need to count them too. Recursion...

Not really sure it's a recursive calculation per se.
As I see things it is just a matter of identifying where - working from "current" date backwards - the 119th "non-text" instance occurred. Then simply a case of summing from that row to the "current" row.
(this is the approach highlighted in the prior array)

FWIW, I wouldn't argue that a multitude of arrays is desirable ...