lumping dates together

[kuder]

If I have a bunch of dates in coulmn A and and numbers associated with the dates in column B, is there a way to get an average from column B based on week?

Example:

20-Jun-11 32
25-May-11 17
21-Apr-11 34
22-Jun-11 19
19-Apr-11 22
24-Jun-11 47
18-Apr-11 25
23-May-11 29
26-May-11 31

I want to see in a separate column, that the average for all dates from 18-Apr to 21-Apr is X. I want to see that the average for all dates the week of 23-May are XX, etc....

Thanks!

[DBY]

Hi
If for example your data was in A2:B10, in C2:C10 you could run down a Weeknum function:

=WEEKNUM(A2)

and in D2:D10 use a combination of Sumif and Countif to average each week's values.

=SUMIF($C$2:$C$10,$C2,$B$2:$B$10)/COUNTIF($C$2:$C$10,$C2)

I believe in Excel 2007 there's an Averageif function but this seems to work in 2003.