+ Reply to Thread
Results 1 to 6 of 6

Weeks of Stock Cover Calculation

  1. #1
    Registered User
    Join Date
    07-24-2015
    Location
    Northamptonshie, England
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Lightbulb Weeks of Stock Cover Calculation

    Hello All,

    Ive worked out how to work out how many weeks of stock cover I have using a fab formula I found online and then adjusted.

    This is it:

    =SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))<=B8))+LOOKUP(0,SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11-B8,(B8-(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11))/B11:$P11)

    This looks at my opening stock in cell B8, My Sales forecast for the next 15 weeks in B11:P11 BUT I have a second set where the sales forecast is not input on one row.

    Im trying to adjust the formula to look at the sales forecast in cells: D9,G9,J9,M9,P9,S9,V9,Y9,AB9,AE9,AH9,AK9,AN9,AQ9 & AT9

    Does anyone know how I can re-write this formula to do this? PLEASE do not suggest a VBA becase I don't get them and don't have the time to try and understand lol!

    Thanks in advance to anyon that can help

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Weeks of Stock Cover Calculation

    Maybe I can't help you, but

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    07-24-2015
    Location
    Northamptonshie, England
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Re: Weeks of Stock Cover Calculation

    Hi - thanks for the reply.

    Ive attached a very basic example showing both versions currently used. The first version (which includes what I have done to calculate the weeks of stock figure) and then the second version where I want to look at the sales forecast every couple of cells, and not in one long row.

    Both have the same information on them and the first version shows the expected results for how many weeks cover the opening stock would give.

    The original file for the second version includes over 500 SKU's and is a very large file. I would rather not change its current format to be like the first- I would rather try to adjust the formula from the first version,

    I hope Im making sense?

    PLAN EXAMPLES.xlsx

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Weeks of Stock Cover Calculation

    Not for me, maybe another forummember.

  5. #5
    Registered User
    Join Date
    07-24-2015
    Location
    Northamptonshie, England
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Re: Weeks of Stock Cover Calculation

    How do I get other people to see and respond to this?

  6. #6
    Registered User
    Join Date
    01-07-2019
    Location
    Worcester, UK
    MS-Off Ver
    10
    Posts
    1

    Re: Weeks of Stock Cover Calculation

    Brilliant formula Suzyanne37. Really helped when calculating stock cover. Been looking for something like this for a long time!! Thanks

    Quote Originally Posted by suzyanne37 View Post
    Hello All,

    Ive worked out how to work out how many weeks of stock cover I have using a fab formula I found online and then adjusted.

    This is it:

    =SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))<=B8))+LOOKUP(0,SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11-B8,(B8-(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11))/B11:$P11)

    This looks at my opening stock in cell B8, My Sales forecast for the next 15 weeks in B11:P11 BUT I have a second set where the sales forecast is not input on one row.

    Im trying to adjust the formula to look at the sales forecast in cells: D9,G9,J9,M9,P9,S9,V9,Y9,AB9,AE9,AH9,AK9,AN9,AQ9 & AT9

    Does anyone know how I can re-write this formula to do this? PLEASE do not suggest a VBA becase I don't get them and don't have the time to try and understand lol!

    Thanks in advance to anyon that can help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-13-2006, 06:25 AM
  2. [SOLVED] Calculate weeks cover
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  3. [SOLVED] Calculate weeks cover
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. Calculate weeks cover
    By \Kevin Carroll via OfficeKB.com\ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Calculate weeks cover
    By \Kevin Carroll via OfficeKB.com\ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  6. Calculate weeks cover
    By \Kevin Carroll via OfficeKB.com\ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. Calculate weeks cover
    By \Kevin Carroll via OfficeKB.com\ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Calculate weeks cover
    By \Kevin Carroll via OfficeKB.com\ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2005, 08:05 PM

Tags for this Thread

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