average last x weeks of data, based on current week

**shifei** · 09-16-2010, 05:09 AM

Hi,
I would appreciate some help with a problem i have on my spreadsheet.

I have a column with week numbers (N58:N109) and a column with the number of items used per week (O58:O109). The weeks are from week 1 to week 52. the useage column automatically updates daily (it is linked).
I also have todays weeknumber displayed in cell F1 (based on today's date).

Is it possible to get the average of the last 8 weeks, based on todays week number?

so for example this is week 37, i would want to see the average number of items per week from week 30 to the current week.

and next week it would display from week 31-38 and so forth.

I hope that is clear, thanks in advance for your help!

Shifei

**canapone** · 09-16-2010, 06:01 AM

Hi, you could try with a array formula, in F1 these the today's weeknumber

=AVERAGE(IF((N58:N109>=F1-7)*(N58:N109<=F1),(O58:O109)))

to be confirmed with control+shift+enter.

Jambo,

**sweep** · 09-16-2010, 06:05 AM

Hi,

This (non volatile) formula should work for you

Please Login or Register  to view this content.

**shifei** · 09-16-2010, 06:10 AM

Thanks!
Is there a way i can get it to not include zero entries? (some weeks there is no useage of an item). so still only the last 8 weeks, but not including zero entries.

thanks again!

Shifei

**daddylonglegs** · 09-16-2010, 06:23 AM

Do you only have data for this year? What will happen when you get to next year, in week 1 or week 4, what data would you consider then?

**shifei** · 09-16-2010, 06:28 AM

hi daddylonglegs

yes, data is only for this year. i make a new file for each year. so week 52 is the last week of this year.

shifei

**canapone** · 09-16-2010, 07:07 AM

Hi, array formula (again) to be confirmed with control+shift+enter, hoping it helps you.

=AVERAGE(IF((N58:N109>=F1-7)*(N58:N109<=F1)*(O58:O109<>0),(O58:O109)))

Zeros or empty cells are not considered in the average.

-------------------------------------------------------
Alternative: not array formula:

=SUMPRODUCT(((N58:N109>=F1-7)*(N58:N109<=F1)*(O58:O109)))/SUMPRODUCT(((N58:N109>=F1-7)*(N58:N109<=F1)*(O58:O109<>0)))