Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Specific date in Biwwekly Based on date | Excel Discussion (Misc queries) | |||
Lookup to find the last value based on name and date field | Excel Discussion (Misc queries) | |||
Find date and copy range based on that date | Excel Programming | |||
Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |