Auto-update values (guessing, IF formula...)

Hi, can I ask for a suggestion, how to help eliminate manual work updating file every month?

All details are explained in a file attached, but basically, I get days and weeks moving around with every new month selected, and challenge is: to get end of week results I need to be updating end of week formulas for every new month. Such a waste of time...

Here is a file:

https://www.excelforum.com/attachmen...1&d=1506882379

There are existing formulas there already, only they dont do a proper work...

Thanking in advance!!

=IFERROR(IF(AH$2=0,,COUNTIFS(INDEX($C5:$AG5,AG$2+1):INDEX($C5:$AG5,AH$2),"Y")/SUM(COUNTIFS(INDEX($C5:$AG5,AG$2+1):INDEX($C5:$AG5,AH$2),{"y","n"}))),)
and date of the month in AF1

Thank you so much Tim, it works!! I dont know how you guys do it... its genius :-)

It works too, Root_ Awesome, thank you so much guys!

Thank you protonLeah, works beautifully!

@Root_ :

I worked applying your formula, which is cool, only I bumped into issue, when added more rows and trying to get average for all rows.

Explained details are in the excel file attached, would it be possible for you to take a look and suggest if your given formula could be adapted for a new condition?

Thanking in advance!

https://www.excelforum.com/attachmen...1&d=1506946581