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 ?