+ Reply to Thread
Results 1 to 15 of 15

Calculating Inventory Sell Through by month based on multiple criteria

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Calculating Inventory Sell Through by month based on multiple criteria

    I am trying to calculate inventory sell through by month, but am running into a lot of issue. Below is a sample data set. The month will only reach 100% sell through when the quantity sold equals the amount received in a month. In the below sample, you see that 2014-04 achieved 100% sell through because 1 was sold in April, 2 in May, and 7 in June (equaling 10). 2014-05 achieves 87% sell through because there were 5 sold in July, 4 in August, 3 in Sept, and 1 in October (equaling 13). The other issue is that there would be 200,000 rows in the Excel sheet that contain multiple different SKU's, so the formula would have to look at each SKU and calculate only on the group of those similar SKU's. Please advise if I can provide any additional details.



    HTML Code: 
    351846
    Attached Images Attached Images
    Last edited by AlphaSkidz; 10-14-2014 at 06:05 PM.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    How about posting a sample workbook?
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Here is a test file

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Bump......

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Sorry I have to make a pass on this one, at least for now. The task is interesting and well defined so it should spark some interest here on the forum. It may however be more of a VBA task, it is rather advanced.

  6. #6
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Should I post this in the VBA section also?

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    No need to Jacc has put it before the "Cavalry"

    If someone doesn't solve this in the interim - I'll have a look at it tomorrow
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,868

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Don't know if this helps or not but with a few helper columns and an array formula I got this to work for your data set.

    1. Please check with your extended data set though because it was only tested on that set
    2. I know the purists on this forum don't like helper columns and some of these could be reduced by incorporating into formulas (I think) but I left it open for you to check easily and frankly it would take a brighter person than me to try and get them all into one formula
    3. I had to use an array formula so remember cntl+shift+enter

    Good luck

    Example(1).xlsx
    Happy with my advice? Click on the * reputation button below

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Try this - in G6 enter:
    =IF(E6=0,G5,MEDIAN(0,1,(SUMIF($D$6:$D$19,D6,$F$6:$F$19)-SUMIF(D$5:D5,D6,E$5:E5))/E6))
    and fill down.
    Remember what the dormouse said
    Feed your head

  10. #10
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Crooza/Romper, both of these work...However, they are quite the resource hog. I have 200,000 rows of data and Excel keeps stalling trying to calculate the formulas.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Can your data be sorted by SKU?

  12. #12
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    It is, It's sorted by SKU and then by Date.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    See if this version works better - note the two additional columns A and B - these could be incorporated into the formula if necessary.
    Attached Files Attached Files

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Hi AlphaSkidz,

    Here's a VBA solution:

    Please Login or Register  to view this content.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Calculating Inventory Sell Through by month based on multiple criteria

    Here's a quicker version - but the example I saw only went from column C to Column G and from row 4 to row 18??

    Please Login or Register  to view this content.
    Last edited by xladept; 10-16-2014 at 11:40 PM.

+ 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. [SOLVED] Calculating list data based on multiple criteria
    By squigman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2013, 07:35 PM
  2. Calculating frequency of unique texts within a month under multiple criteria
    By JustinHanamichi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2013, 08:35 PM
  3. [SOLVED] Calculating Average Based on Multiple Criteria
    By Scott_88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 07:05 AM
  4. Calculating Inventory at the End of the Month
    By mitylene in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2009, 04:43 AM
  5. Calculating percentages based on multiple criteria
    By F6Hawk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2007, 11:05 PM

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