Trying to find averages based on date.

[Guest]

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]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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