ExcelBanter - rolling average

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - rolling average (https://www.excelbanter.com/excel-worksheet-functions/84623-rolling-average.html)

I have a master sheet and three more sheet (opening stock,receipts &
closing stock) and within these 3 sheets there is product code ,
description price and the week numbers(i.e from wk 12 to wk 52) which
have quantities. I want to put a function in master sheet that will
calculate the sales rolling average based on week number eg
If i take 1 week average if i am in wk 15 it should give me cellvalue
of wk 14, and in 13 it gives me week 12 value.
If its 2 weeks average if iam in week 15 it should give an average of
week 14and 13, if am in week 14 it should give me the average for week
13 and 12.
If its 3 weeks and am in week 17, it should give me the average for
week 14,15,16 while in week 15 it should give me average for week
12,13,and 14

Can sb assist???

--
William Okumu
------------------------------------------------------------------------
William Okumu's Profile: http://www.excelforum.com/member.php...o&userid=33745
View this thread: http://www.excelforum.com/showthread...hreadid=535206

Assuming you have week no. in column A, enter in column B:

3 weeks average:
=AVERAGE(OFFSET('Opening stock'!B$1,A1-4,0,3,1))

2 weeks average:
=AVERAGE(OFFSET('Opening stock'!$B$1,$A1-3,0,2,1))

That will return #REF for the n first weeks.

HTH
--
AP

For
"William Okumu"
a écrit dans le message de
news:William.Okumu.26o3fa_1145712001.9352@excelfor um-nospam.com...

I have a master sheet and three more sheet (opening stock,receipts &
closing stock) and within these 3 sheets there is product code ,
description price and the week numbers(i.e from wk 12 to wk 52) which
have quantities. I want to put a function in master sheet that will
calculate the sales rolling average based on week number eg
If i take 1 week average if i am in wk 15 it should give me cellvalue
of wk 14, and in 13 it gives me week 12 value.
If its 2 weeks average if iam in week 15 it should give an average of
week 14and 13, if am in week 14 it should give me the average for week
13 and 12.
If its 3 weeks and am in week 17, it should give me the average for
week 14,15,16 while in week 15 it should give me average for week
12,13,and 14

Can sb assist???

--
William Okumu
------------------------------------------------------------------------
William Okumu's Profile:
http://www.excelforum.com/member.php...o&userid=33745
View this thread: http://www.excelforum.com/showthread...hreadid=535206

Here is an example of what you want on the closing stock average

=AVERAGE('Closing stock'!Q2:INDEX( 'Closing
stock'!A2:Q2,SUMPRODUCT(LARGE(COLUMN('Closing stock'!A1:Q1)*('Closing
stock'!A2:Q2<""),3))))

column Q is the last possible column that data will be ( so will probably be
in the are of AZ/BA/BB). Column A is not the first column real data will be
in, but an index column, so don't change if say you start week 1 in column
B, lkeave it at A. The ,3))) refers the the number of weeks being averaged.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"William Okumu"
wrote in message
news:William.Okumu.26o3fa_1145712001.9352@excelfor um-nospam.com...

I have a master sheet and three more sheet (opening stock,receipts &
closing stock) and within these 3 sheets there is product code ,
description price and the week numbers(i.e from wk 12 to wk 52) which
have quantities. I want to put a function in master sheet that will
calculate the sales rolling average based on week number eg
If i take 1 week average if i am in wk 15 it should give me cellvalue
of wk 14, and in 13 it gives me week 12 value.
If its 2 weeks average if iam in week 15 it should give an average of
week 14and 13, if am in week 14 it should give me the average for week
13 and 12.
If its 3 weeks and am in week 17, it should give me the average for
week 14,15,16 while in week 15 it should give me average for week
12,13,and 14

Can sb assist???

--
William Okumu
------------------------------------------------------------------------
William Okumu's Profile:
http://www.excelforum.com/member.php...o&userid=33745
View this thread: http://www.excelforum.com/showthread...hreadid=535206

Thx Bob for the idea it worked but i also had to include other
constraints
THanks anyway

--
William Okumu
------------------------------------------------------------------------
William Okumu's Profile: http://www.excelforum.com/member.php...o&userid=33745
View this thread: http://www.excelforum.com/showthread...hreadid=535206