Avg monday,Tuedsday

[John Bruel]

Hello,

How can I look in a column for a day (MON,TUES, WED,
etc.) and average the value from another column. My data
looks like this:
12-Feb-02 -0.0003
13-Feb-02 -0.0055
14-Feb-02 0.0060
15-Feb-04 0.0060
16-Feb-04 0.0060
17-Feb-04 0.0060
18-Feb-04 -0.0030
19-Feb-04 0.0060
20-Mar-04 0.0060
21-Mar-04 -0.0040
22-Mar-04 0.0060
etc. etc

Thank you

 

[Bob Phillips]

Hi John,

One way

=SUMPRODUCT((TEXT(A1:A11,"ddd")="Mon")*B1:B11)/SUMPRODUCT(--(TEXT(A1:A11,"dd
d")="Mon"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
I'd suggest using the WEEKDAY function to prevent thats this formula
won't work in different language versions:

=SUMPRODUCT(--(WEEKDAY(A1:A11)=2),B1:B11)/SUMPRODUCT(--(WEEKDAY(A1:A11)
=2))

 

[Harlan Grove]

I'd suggest using the WEEKDAY function to prevent thats this formula
won't work in different language versions:

=SUMPRODUCT(--(WEEKDAY(A1:A11)=2),B1:B11)
/SUMPRODUCT(--(WEEKDAY(A1:A11)=2))

....

Why use two SUMPRODUCT calls? The following array formula would be more
recalc efficient, and it might use less storage (untested).

=AVERAGE(IF(WEEKDAY(A1:A11)=2,B1:B11))