+ Reply to Thread
Results 1 to 6 of 6

rolling average

  1. #1
    Registered User
    Join Date
    04-22-2006
    Posts
    29

    rolling average

    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???

  2. #2
    Ardus Petus
    Guest

    Re: rolling average

    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" <[email protected]>
    a écrit dans le message de
    news:[email protected]...
    >
    > 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
    >




  3. #3
    Bob Phillips
    Guest

    Re: rolling average

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >




  4. #4
    Registered User
    Join Date
    04-22-2006
    Posts
    29
    Thx Bob for the idea it worked but i also had to include other constraints
    THanks anyway

  5. #5
    Registered User
    Join Date
    04-22-2006
    Posts
    29
    Thx Bob for the idea it worked but i also had to include other constraints
    THanks anyway

  6. #6
    Registered User
    Join Date
    04-22-2006
    Posts
    29
    Thx Bob for the idea it worked but i also had to include other constraints
    THanks anyway

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1