Cumulative sum for weekly average hours over a month

I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday.

In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday.

Please see attached file. I am referring to columns J and K.

A simple explanation of the solution would help too.

Many thanks,

Alis

A few approaches, one would be to use an AVERAGE(IF array:

K10: =AVERAGE(IF(($B$4:$B10>$B10-28)*(I$4:I10<>""),I$4:I10))
committed with CTRL + SHIFT + ENTER
copy to L10

Be warned however that arrays are expensive performance wise...

(the above assumes that you're not running XL2007, if you are you can make use of the AVERAGEIF which does not require CTRL + SHIFT + ENTER)

works for me but I did alter the operator from >= to > (@ 10.36am UK time) so if you used the example before then it would have been off slightly ...

Are you entering as an array as advised (ie with CTRL + SHIFT + ENTER)

It would be better to post a sample with more complete data such that multiple tests can be conducted.

Not clear I'm afraid - what governs no data - the current week only, ie:

K10: =IF(AND($I10,$C10="Sat"),AVERAGE(IF(($B$4:$B10>$B10-28)*(I$4:I10<>""),I$4:I10)),"")
committed with CTRL + SHIFT + ENTER

The above will for ex. only execute the AVERAGE array if the sum of current weekly hours > 0 (as stipulated by I10 - itself the sum of G range)

see prior post