Avg monday,Tuedsday

  • Thread starter Thread starter John Bruel
  • Start date Start date
J

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
 
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)
 
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))
 
Frank Kabel said:
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))
Click to expand...
....

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))
 
Back
Top