SUMPRODUCE and AVERAGE

[edwardpestian]

I have the following SUMPRODUCT formula that sums tha values in th
range B6:AF6 based on the date in AL3. It takes the date in AL3 an
returns the sum of the values starting with the Monday before the dat
in AL3 up to and including the date in AL3. B5:AF5 is the date rang
and B6:AF6 is the data range.

=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1),--($B$5:$AF$5<=$AL$3),$B6:$AF6)

I need to find the average of the sum returned. So if the date in AL
was 07/19/06 (Wed), the SUMPRODUCT formula above would return the su
of the values in B6:AF6 for 07/17, 07/18 and 07/19 (Mon-Wed) and divid
that sum by 3 (total days in range).

Any ideas?

Thanks.

-e

 

[Dav]

If youre formula is working correctly and
=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1),--($B$5:$AF$5<=$AL$3),$B6:$AF6
returns the sum

=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1),--($B$5:$AF$5<=$AL$3))

Will return the count

Divide the first by the second

I always multipy my terms eg
=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1)*($B$5:$AF$5<=$AL$3)
as this seems to work better


Regards

Da

 

[edwardpestian]

Worked Great!

Thanks Dav...

-e

 

[daddylonglegs]

You could also use a shorter "array" formula

=AVERAGE(IF(($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,3))*($B$5:$AF$5<=$AL$3),$B6:$AF6))

confirmed with CTRL+SHIFT+ENTE