Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008

    Unanswered: Index to return Forecasting Date

    I need to create a formula in a single cell without further cells being
    used for calculations.

    I'm trying to return the date when my Available stock will run out based on a fortnightly forecast.

    A B C D E F G
    1 CODE Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
    2 NET001 1/09/2008 64000 36000 0 0 48000

    I put the formula in B2. Which attempts to return the date from the 1st row.

    Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

    Problem then is that once I start included the rest of the forecast numbers
    the MATCH only looks for the closing matching number.

    H1 = 27-Oct
    H2 = 144,000
    I1 = 10-Nov
    I2 = 96,000

    Change the Avail Number (C2) to 150,000

    Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
    which is wrong - It should return 27-Oct because it is within that period
    that my stock will run out.

    Any suggestions?

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    You can't achieve this with Excel without making some preetty radical changes.

    The MATCH function is working perfectly according to the Help file - according to the arguments supplied, it's looking for the largest value in lookup_array that is less than or equal to lookup_value. It does not perform any comparisons or other functions on the values within lookup_array.

    I've managed to get the right answer (according to what you need) on the attached spreadsheet, by doing the following:
    • Reversing the order of the dates and their forecast quantities
    • Adding a row of calculated cells to get the running forecast (please note the semi-anchored formula)
    • Changing the match_type arguement to -1

    Hope this helps!
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2008

    Index to return Forecasting Date

    Thanks for your reply.

    I did get an answer for it eventually.

    Where $AM$3:$AY$3 contains my fortnightly forecast dates

    Where $AM4:$AY4 contains my fortnightly forecast qty's

    And $H4 is my available Stock.

    {=INDEX($AM$3:$AY$3,MATCH(TRUE,SUBTOTAL(9,OFFSET($ AM4:$AY4,,,,COLUMN($AM4:$AY4)-MIN(COLUMN($AM4:$AY4))+1))>$H4,0))}

    Ctrl + Shift + Enter (Array)

    It works well.



Posting Permissions

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