Calculating only certain weekdays

[Chris Boone]

Hello everyone,

I have a spreadsheet that looks similar to this:

Wed 1/1/2003 71
Thu 1/2/2003 204
Fri 1/3/2003 145
Sat 1/4/2003 60
Sun 1/5/2003 40
Mon 1/6/2003 203
Tue 1/7/2003 182
Wed 1/8/2003 228
Thu 1/9/2003 175
Fri 1/10/2003 149
Sat 1/11/2003 112
Sun 1/12/2003 125
Mon 1/13.2003 131
Tue 1/14/2003 139
Wed 1/15/2003 218


I want to get a mean (average) of all of the Wednesdays, then all of the
Thursdays, and so forth. The spreadsheet has over a year's worth of
entries, so I long ago reached the limit of just entering individual cells,
i.e. AVG(b1, b8, b15,...etc.). Does anyone know of a simple way that will
allow me to garner an average for the entire range of data?

Thanks, in advance.

- Chris

 

[Tom Ogilvy]

=Average(if(weekday(B1:B2000)=2,C1:C2000))

Entered with Ctrl+shift+Enter rather than just Enter since this is an array
formula

See the weekday function in Excel help for definitions of which day returns
which number.

Also, this assumes you dates are in Column B and values in Column C. Adjust
as necessary.

 

[Chris Boone]

Okay, but I did not use any formatting or the DATE function on the date
cells. Will this solution still work?

Thanks for your help,
- Chris

 

[Intruder9]

Well if it was me I would highlight the entire column and then use DATA
TEXT TO COLUMNS then choose the Delimited box and then Next, as a delimiter
choose space and then finish. Now use an array formula (entered with Ctrl
Shift and Enter keys pressed together) Say your data ended up in the range
C4:E18 then your formula would end up looking like {=SUM((C4:C18="Wed") *
E4:E18)} and giving an answer of 517. If you need help with it drop me an
email.

 

[Intruder9]

Woops I meant {=AVERAGE((C4:C18="Wed") * E4:E18)}

 

[Intruder9]

Sorry, In too much of a hurry, should be
{=AVERAGE(IF(C4:C18="Wed",E4:E18))}