Trying to find averages based on date.
 

I have a series of columns for date, total project hours, and hours per team
(three different teams - one column each).

what I want to do is get averages for each team hour column based on date
ranges. i.e. - during this six month period, this team averaged this number
of hours.

what I need help doing is how do I get excel to search the date column,
collect the corresponding team hours for entries that fall within a defined
range of dates, and then average those hours? I'm assuming it would be a
multiple step process and not a single formula, but my biggest roadblock is
the first step - how to collect the hours based on the date.

Any help anyone can offer will be greatly appreciated!

Thanks
Daesthai

Trying to find averages based on date.
 

Maybe you could use a formula like:

=sumproduct(--(a1:a100=date(2006,1,1)),--(a1:a100<date(2006,7,1)),
--(b1:b100="team1"),(c1:c100))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Daesthai wrote:

I have a series of columns for date, total project hours, and hours per team
(three different teams - one column each).

what I want to do is get averages for each team hour column based on date
ranges. i.e. - during this six month period, this team averaged this number
of hours.

what I need help doing is how do I get excel to search the date column,
collect the corresponding team hours for entries that fall within a defined
range of dates, and then average those hours? I'm assuming it would be a
multiple step process and not a single formula, but my biggest roadblock is
the first step - how to collect the hours based on the date.

Any help anyone can offer will be greatly appreciated!

Thanks
Daesthai

--

Dave Peterson