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

...Attached

Flattery gets you everywhere...

First off be sure to make use of Excel's Evaluate Formula tool... you can step through an entire formula to see how it is analysed (you can also use the formula bar and highlight a part of interest and press F9 to see how it evaulates)...

Using example of K10:

Please Login or Register  to view this content.

The main bit is the AVERAGE Array... the outer IF is really just to say only conduct the AVERAGE Array if both I10 has a numerical value <> 0 and C10 is Sat... else return Null.

So the AVERAGE...

Please Login or Register  to view this content.

Here we're creating a set of values to be Averaged... however we're using an IF to populate the set, we will only populate the set with the value in Column I if our IF test returns TRUE, in truth it will be hard to explain with the above given there's little data so let's simplify the example as follows...

Consider range A1:B5

Please Login or Register  to view this content.

Let's say we want the Average of Column B but only where Column A is "a" and the value in Column B is > 0, our formula is thus:

Please Login or Register  to view this content.

Here we're populating our set of values based on the result of our IF... an IF as you know works like:

Please Login or Register  to view this content.

Notes:

-- when the test in the IF is numerical we can make use of the fact that in XL terms only the value 0 equates to FALSE... every other value is TRUE.

-- a Boolean TRUE/FALSE when coerced to an integer (via *, + etc...) becomes 1/0 respectively

-- you have probably spotted that we don't set a FALSE argument in our Average Array - when not specified a Boolean of FALSE is automatically generated.

So going back to our sample... the various elements are evaluated as so:

Please Login or Register  to view this content.

The 1st set of Booleans relate to whether or not A1:A5 = "a"
The 2nd set of Booleans relate to whether or not B1:B5 > 0

These 2 sets of Booleans are then coerced via multiplication such that the set can be condensed as follows:

Please Login or Register  to view this content.

The 1st set is the result of multiplying the 1st set of Booleans by the 2nd ,eg:

Please Login or Register  to view this content.

As discussed we know 0 equates to FALSE so in our IF we're going to exclude any values in the 2nd set where the first set = 0 ... so in reality the above becomes the below after the IF is evaluated:

Please Login or Register  to view this content.

the Average function will only use Numerical values in the set .. the Boolean FALSE values will be ignored so in essence we end up with:

Please Login or Register  to view this content.

Result being 3.