Trying to create a rolling 12 weekly tally

[MarkyT1973]

Hello, im stuck. row 1 is a weekly date for 52 weeks.
row 2 is attendance… x for attending, blank for non attendance.

im looking for a way of having the 53rd column row 2 showing compliance.
that is to say of the “last” rolling 12 weeks 8 of those 12 must have an attendance.
If there is 8 then a letter c or blank if not. (but i need it to roll every week so that when 12 becomes 13 the next week its ignored.

EDIT; only the current 12week so i guess the last one. the previous 12 week does not matter once over. windows 10
LO 7.4.4.2 x64 …save format excel 2007 365

Does anyone know how I could achieve such a formula?
many thanks

[ajlittoz]

Please edit your question (don’t use a comment) to improve your specification. There are 41 12-week sequences in a year. You can “consolidate” attendance in row 3 starting in column 12 up to 52 where this cell will tell if attendance is compliant during the period.

Now, do you want to assert if any of these sequences is compliant? or only the last one? – your specification above is not clear on this point –

Once you have the outcome of every sequence, you can merge the results in the 53rd column. But if you want to know the number of the first compliant sequence, it is a bit more difficult but not impossible.

PS: don’t forget to mention OS name, LO version and save format.

EDIT
As you save M$ Excel format, you’ll continuously convert back and forth from internal representation to alien format. This is not favourable to document stability and reliability. It makes also difficult to design formulas because they must use the common intersection of both formats.

[Villeroy]

attendance.ods (15.5 KB)
A list of all attendances in column A.
A cell calculating the Monday 12 weeks ago.
Another cell calculating the attendances since then.
A conditional formatting marking duplicates in red.

Insert a cell anywhere within the list and enter a new attendance date.

[ajlittoz]

If I understood the problem here is a proof-of-concept, adapt to fit your needs:
AskLORollingAttendance.ods (13.5 KB).

Row 1: week number
Row 2: attendance as 1 if present, void or 0 if absent
Row 3: sum of attendance over the past 12 weeks, including current week
Row 4: compliance is 1 if sum in cell above if equal or greater than 8
I have also added a conditional formatting to paint a green background if compliance holds.