Average of Day and Time
 

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

Average of Day and Time
 

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



--
__________________________________
HTH

Bob

"Jarod" wrote in message
...
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

Average of Day and Time
 

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" wrote:

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

Average of Day and Time
 

That suggest you matched none.

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

--
__________________________________
HTH

Bob

"Jarod" wrote in message
...
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" wrote:

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