Adapting ave formulas to changing weeks

Hi guys, maybe you'd be able to figure the way of adapting average formulas to existing one's that are used to calculate values based on changing weeks?

I'm including excel file with all the details explained, but generally its about finding the way how to blend two working formulas together, to get the desired outcome.

Would it be possible for you to take a look? Thanking in advance!

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

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)

Thanks Jonmo1. Not able to add more columns between B and C, as days are changing with every different month selected. So it would never be first 7 cells for 7 days (this also a case in excel copy returned by yourself) But thanks for looking into it!

@Richard - your suggested option works well! I tested it, by adding WK1, WK2 etc manually. May I please ask for a guide what formula could be applied to cells C2:AG2 to have WK1 WK2 etc adapt to changing weeks, for each different month selected? I'm attaching a file updated, with just those formulas left outstanding, to write off this challenging task...

Thanking you guys for all of your help, once again!

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

I'm aware of that, that's why the day numbers still begins in the appropriate weekday box. As shown in the example i Posted before.

Here's a full workup.
I added columns on both ends of the table to accommodate 6 whole (complete m-s) weeks
Find the first Monday before the 1st of the month in C4
=WORKDAY.INTL((2&AH1&AB1)+0,-1,"0111111")
Then D5 filled to AR5 is just
=C5+1
Then just manually put Mon Tue Wed across row 3 (that can be done by just auto filling once you have a couple done by hand)

The row of Day numbers adjusts automatically when you make your choices of year and month.

Then your formula for Row 10 is
=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)

Similar for Rows 5-9 (just focusing on single row, instead of all 5 rows)
=IFERROR(COUNTIFS(OFFSET($C5:$I5,0,(COLUMNS($A1:A1)-1)*7),"Y")/SUM(COUNTIFS(OFFSET($C5:$I5,0,(COLUMNS($A1:A1)-1)*7),{"y","n"})),0)

You can probably adjust your conditional formatting to grey out (or whatever) the columns that are not within the specified month

Hang on, that is still the same attachment as before..

Here is the full workup of my suggestion

Originally Posted by vill

Hi guys, maybe you'd be able to figure the way of adapting average formulas to existing one's that are used to calculate values based on changing weeks?

It would have been courteous to mention who developed those existing formulas -- don't you think so?

Anyway, the solution is pretty simple -- change just four numbers:

=IFERROR(COUNTIFS(INDEX($C5:$AG5,DAY(WORKDAY.INTL($C$4-1,RIGHT(AH$4,1)-1,"1111110")+1)):INDEX($C9:$AG9,DAY(MIN(WORKDAY.INTL($C$4-1,RIGHT(AH$4,1),"1111110"),EOMONTH($C$4,0)))),"Y")/SUM(COUNTIFS(INDEX($C5:$AG5,DAY(WORKDAY.INTL($C$4-1,RIGHT(AH$4,1)-1,"1111110")+1)):INDEX($C9:$AG9,DAY(MIN(WORKDAY.INTL($C$4-1,RIGHT(AH$4,1),"1111110"),EOMONTH($C$4,0)))),{"y","n"})),0)

@Root_ : that was a formula I was looking for! Replied to that same post you mention, but since it was already marked as SOLVED and there was no replies, thought I lost the chance to be heard there, posted a comment here. It's like you said, "it's plug-n-play -- no helper rows, no changes to your layout, no named ranges" - would be spot on, really needed to make it work in such way, all the way. So thank you for your courage and sharing that lengthy formula :-)

@Richard - this too what was looking for, and already know where else the same will get applied. So thank you!

@Jonmo1 & @protonLeah - thank you for your input! I cant tell how many times I was jumping in my chair, saying, 'whoa, just like that?? these guys are genius!!' You really are..

You're all amazing, thank you so much...