software

I have a master sheet and three more sheet (opening stock,receipts amp;
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: www.excelforum.com/member.php...oamp;userid=33745
View this thread: www.excelforum.com/showthread...hreadid=535206Assuming 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
quot;William Okumuquot; gt;
a écrit dans le message de
news:William.Okumu.26o3fa_1145712001.9352@excelfor um-nospam.com...
gt;
gt; I have a master sheet and three more sheet (opening stock,receipts amp;
gt; closing stock) and within these 3 sheets there is product code ,
gt; description price and the week numbers(i.e from wk 12 to wk 52) which
gt; have quantities. I want to put a function in master sheet that will
gt; calculate the sales rolling average based on week number eg
gt; If i take 1 week average if i am in wk 15 it should give me cellvalue
gt; of wk 14, and in 13 it gives me week 12 value.
gt; If its 2 weeks average if iam in week 15 it should give an average of
gt; week 14and 13, if am in week 14 it should give me the average for week
gt; 13 and 12.
gt; If its 3 weeks and am in week 17, it should give me the average for
gt; week 14,15,16 while in week 15 it should give me average for week
gt; 12,13,and 14
gt;
gt; Can sb assist???
gt;
gt;
gt; --
gt; William Okumu
gt; ------------------------------------------------------------------------
gt; William Okumu's Profile:
www.excelforum.com/member.php...oamp;userid=33745
gt; View this thread: www.excelforum.com/showthread...hreadid=535206
gt;
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:Q2lt;gt;quot;quot;),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)

quot;William Okumuquot; gt;
wrote in message
news:William.Okumu.26o3fa_1145712001.9352@excelfor um-nospam.com...
gt;
gt; I have a master sheet and three more sheet (opening stock,receipts amp;
gt; closing stock) and within these 3 sheets there is product code ,
gt; description price and the week numbers(i.e from wk 12 to wk 52) which
gt; have quantities. I want to put a function in master sheet that will
gt; calculate the sales rolling average based on week number eg
gt; If i take 1 week average if i am in wk 15 it should give me cellvalue
gt; of wk 14, and in 13 it gives me week 12 value.
gt; If its 2 weeks average if iam in week 15 it should give an average of
gt; week 14and 13, if am in week 14 it should give me the average for week
gt; 13 and 12.
gt; If its 3 weeks and am in week 17, it should give me the average for
gt; week 14,15,16 while in week 15 it should give me average for week
gt; 12,13,and 14
gt;
gt; Can sb assist???
gt;
gt;
gt; --
gt; William Okumu
gt; ------------------------------------------------------------------------
gt; William Okumu's Profile:
www.excelforum.com/member.php...oamp;userid=33745
gt; View this thread: www.excelforum.com/showthread...hreadid=535206
gt;

Thx Bob for the idea it worked but i also had to include other
constraints
THanks anyway--
William Okumu
------------------------------------------------------------------------
William Okumu's Profile: www.excelforum.com/member.php...oamp;userid=33745
View this thread: 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: www.excelforum.com/member.php...oamp;userid=33745
View this thread: 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: www.excelforum.com/member.php...oamp;userid=33745
View this thread: www.excelforum.com/showthread...hreadid=535206