Counting weeks in years

Hi,
I have two problems:

1. I have the list of days and week from 2007 to October 2010 and I would like to number the weeks not in sequence ( I have now 200 weeks in total in column A and F), but for every year from 1 to 52 weeks.

2. In the H colum I have to insert the average of the data refering to each year week ( ex. average of values of 1st week of 2007, etc) and in the I column the sum of data refering to each week ( ex. sum of values of 1st week of 2007). I'm afraid to take into account all the ( for example) number 1 weeks of all the years ( instead I just want unique values).

Could you please help me?

ps: this forum it's really very useful!

Based on your definition of week 1 ( starts Jan 1st 2007) a Pivot Tale can be of help ( see attached)

Originally Posted by scottylad2

for the first part, use the weekmum argument, with 2 as your 2nd argument

Thank you!

Originally Posted by arthurbr

Based on your definition of week 1 ( starts Jan 1st 2007) a Pivot Tale can be of help ( see attached)

yeah, I was thinking about a pivot, but I would prefer not using it ( my chief doesn't like pivot very much!)...other suggestions for the scond part?

What kind of solution does your chief prefer ? There are different ways of solving the problem

the best, the stablest and fastest way is a pivot tab
there is no need in complications
see this

Originally Posted by daddylonglegs

If you want to sum column C for dates in the week beginning with the date in G3 then try this formula in row 3 copied down

=SUMPRODUCT((B$3:B$1000>=G3)*(B$3:B$1000<G3+7),C$3:C$1000)

for an average you could just divide that by 7....or do you want to exclude zero/blank values?

I think this is the solution I needed! No, I don't need to exclude zero/blank values. I know that with a pivot table things would be easier, but my chief prefer to see directly formulas and functions ( I don't know why!). thank you very much to every body

Cheers

Originally Posted by daddylonglegs

Note that if you use my suggestion for summing the week numbers are incidental, you get a sum for each 7 day period based on the start date of that week.

WEEKNUM doesn't always give you 7 day weeks at the start/end of the year so I suggest that for consistency with the above you use ISO week numbers, which is the European norm, whereby all weeks have 7 days and week 1 starts on the first Monday on or after 29th December. To calculate the ISO week number based on a date in G3

=INT((G3-WEEKDAY(G3,2)-DATE(YEAR(G3+4-WEEKDAY(G3,2)),1,4))/7)+2

thank you again!

Originally Posted by daddylonglegs

If you want to sum column C for dates in the week beginning with the date in G3 then try this formula in row 3 copied down

=SUMPRODUCT((B$3:B$1000>=G3)*(B$3:B$1000<G3+7),C$3:C$1000)

for an average you could just divide that by 7....or do you want to exclude zero/blank values?

Just to my own interest: how should the formula change to exclude blank values?

thank you very much!!