have listing of stores and weekly sales, want to calculate the last four
weeks moving average, e.g, this week (wk4) would be weeks 1-4, next week
(wk5) would be weeks 2-5 and so forth.

second caluculation would be to rank the stores by weekly sales on same four
week moving average.
thx

This can be done - but can you describe the layout of your data please.
Otherwise it's too confusing.

"reno" wrote:

have listing of stores and weekly sales, want to calculate the last four
weeks moving average, e.g, this week (wk4) would be weeks 1-4, next week
(wk5) would be weeks 2-5 and so forth.

second caluculation would be to rank the stores by weekly sales on same four
week moving average.
thx

Store# run down Col1, then staring in col10..62 would be weekly sales,
cols2-9 are max qty sold, total sales, overall average sales, overall sales
rank and a few others, I am looking to put the 4 week moving average in col
8, 4 week moving rank in col 9

"dhstein" wrote:

This can be done - but can you describe the layout of your data please.
Otherwise it's too confusing.

"reno" wrote:

have listing of stores and weekly sales, want to calculate the last four
weeks moving average, e.g, this week (wk4) would be weeks 1-4, next week
(wk5) would be weeks 2-5 and so forth.

second caluculation would be to rank the stores by weekly sales on same four
week moving average.
thx

OK so let's look at the formula needed in Cell H2. This should be the 4 week
moving average for Store #1 - I assume you have a header row.

=(INDEX($J2:$BI2,1,WEEKNUM(NOW(),1))+INDEX($J2:$BI 2,1,WEEKNUM(NOW(),1)-1)+INDEX($J2:$BI2,1,WEEKNUM(NOW(),1)-2)+INDEX($J2:$BI2,1,WEEKNUM(NOW(),1)-3))/4

This could be simplified if you had a value of WEEKNUM(NOW(),1) in a cell
location - then you could refer to that cell. See how you do with that, and
we can do the next part tomorrow


"reno" wrote:

Store# run down Col1, then staring in col10..62 would be weekly sales,
cols2-9 are max qty sold, total sales, overall average sales, overall sales
rank and a few others, I am looking to put the 4 week moving average in col
8, 4 week moving rank in col 9

"dhstein" wrote:

This can be done - but can you describe the layout of your data please.
Otherwise it's too confusing.

"reno" wrote:

have listing of stores and weekly sales, want to calculate the last four
weeks moving average, e.g, this week (wk4) would be weeks 1-4, next week
(wk5) would be weeks 2-5 and so forth.

second caluculation would be to rank the stores by weekly sales on same four
week moving average.
thx