moving average & rank
 

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

moving average & rank
 

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

moving average & rank
 

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

moving average & rank
 

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