Average of Day and Time

[Jarod]

Ok, this is a little weird, but here's the situation:

Column A has the Day of the Week: Monday, Tuesday, etc.
Column B has the numeric Date: 6/10/2008, 6/11/2008, etc.
Column C has the numeric Time: 6:00-6:30AM, 6:30AM-7:00AM, etc.
Column D has the numbers I need to average.

What I need to do is average all the cells in column D (D2 thru D500) based
on the day and time.

For instance:
Monday 6:00-6:30AM: #
Monday 6:30-7:00AM: #
Monday 7:00-7:30AM: #

I tried subtotal and I tried an Average(IF( array formula, but I can't seem
to get anything right. Here's the formula I tried:

{=AVERAGE((IF(A2:A500="Monday",F2:F500,"")))} - gets #DIV/0

 

[Bob Phillips]

=AVERAGE(IF(($A$2:$A$500="Monday")*($C$2:$C$500>--"06:00")*($C$2:$CA$500<=--"06:30"),F2:F500))

 

[Jarod]

Thanks, I tried it, changed $CA$500 to $C$500, but still got #DIV/0 error. I
did use the CTRL-SHIFT-ENTER array commit.

 

[Bob Phillips]

That suggest you matched none.

Is the day column a day, or a date formatted to show just the day?