averaging weekday totals

[soph]

Hi
I used the following formula to sum values for each
weekday in a sheet that contained a value for each 15min
interval for approx a month, where I only had dates (not
days) noted ->
=SUMPRODUCT((WEEKDAY($B$1:$AF$1)=AH$1)*$B2:$AF2)
I am wondering if there is anyway I can get this to
average this sum for each weekday (at each interval) in
order for me to create a day of week template?
Cheers!
Soph

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT((WEEKDAY($B$1:$AF$1)=AH$1)*$B2:$AF2)/SUMPRODUCT((WEEKDAY($B
$1:$AF$1)=AH$1))

 

[Frank Kabel]

Hi Daniel
yes indeed )
thanks for the correction

 

[Daniel.M]

Hi,

=SUMPRODUCT((WEEKDAY($B$1:$AF$1)=AH$1)*$B2:$AF2)/
SUMPRODUCT((WEEKDAY($B$1:$AF$1)=AH$1))

You meant:

=SUMPRODUCT((WEEKDAY($B$1:$AF$1)=AH$1)*$B2:$AF2)/
SUMPRODUCT(--(WEEKDAY($B$1:$AF$1)=AH$1))

Also shorter but an ARRAY formula:

=AVERAGE(IF(WEEKDAY($B$1:$AF$1)=AH$1,$B2:$AF2))

Regards,

Daniel M.

 

[Guest]

Thanks guys!! I really appreciate your help!!
Soph )