Adapting ave formulas to changing weeks

If you add a couple columns to your table between B and C, so that the first week always contains 7 cells
but still start the day numbering at the appropriate day.
So in this case you have 2 blank cells representing Monday and Tuesday.

Then use
=IFERROR(COUNTIFS(OFFSET($C$5:$I$9,0,(COLUMNS($A1:A1)-1)*7),"Y")/SUM(COUNTIFS(OFFSET($C$5:$I$9,0,(COLUMNS($A1:A1)-1)*7),{"y","n"})),0)