1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Question

Discussion in 'Ask an Excel Question' started by Mix1325, Jan 9, 2019.

  1. Mix1325

    Mix1325 New Member

    Messages:
    9
    I have a table with a list of individual material purchases and the date of each purchase. Based on this, I would like to retrieve the next to last purchase of a material.

    For example, material 123456 was purchased in 1/5/2016, 2/3/2017, and 5/8/2018. Based on the material number, I would like to get the next to last purchase which, in this case, would be Feb-17. It would be preferable to retrieve data in the format that I just indicated (i.e., Feb-17).

    I have tried unsuccessfully to use max and large for an array. I would appreciate any help in this matter.
  2. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    Firstly, your idea for using LARGE is correct. An array formula
    = IF( MaterialCode=123456, Date )
    would return a list of the candidate dates with FALSE for the filtered out dates.

    The formula
    = LARGE( IF( MaterialCode=123456, Date ), 2 ) ,
    committed with Ctrl+Shift+Enter, returns the second last date.

    Because the Excel user interface is not really optimised for array formulas you may prefer one of two tricks to avoid CSE. The first is to use a Name to reference the array fragment of the formula, so 'MaterialPurchaseDate' might be defined to refer to
    = IF( MaterialCode=123456, Date )
    reducing the worksheet formula to
    = LARGE( MaterialPurchaseDate, 2 )
    Whereas cell formulas evaluate incorrectly when referencing arrays unless you use CSE, a named formula gets it right.

    The alternative is to use AGGREGATE to effect the LARGE functionality
    = AGGREGATE(14,6, Date/(MaterialCode=123456), 2 )
    Here the filtered data generates an error but the AGGREGATE function is set to ignore errors. Because, for me, the AGGREGATE function is difficult to read, I use further named constants so that my (non-CSE) formula reads
    = AGGREGATE( LARGE, IgnoreErrors, Date/(MaterialCode=123456), 2 )

    The choice is yours :)
  3. Mix1325

    Mix1325 New Member

    Messages:
    9
    Thank you for your reply, Peter! The problem with using =LARGE(MaterialPurchaseDate, 2) is that I receive the next to the last purchase occurrence which maybe in the same month. In my initial request, I did not underscore the fact that I need next to the last MONTH and YEAR of the last purchase. Basically I want to avoid getting the next available date as long as it is within the same month and year

    For example, if the same material was purchased 12/1/2017, 12/15/2017, 12/28/2018, 12/30/2018, my result needs to be Dec-17(i.e., 12/15/2017) as opposed to Dec-18 (i.e., 12/28/2018). My attachment will hopefully clarify.

    Attached Files:

  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    Hi, the exact form of the solution depends upon a number of factors.
    1. Are you willing to accept a helper column containing the purchase month?
    2. Is the solution a lookup for a specific purchase or is a prior month required for every purchase?
    3. Is an Office2016/Office365 solution acceptable

    The last points would make
    = MAXIFS( Month, Month, "<"&Month, materialCode, materialCode )
    viable as a solution.

    Attached Files:

  5. Mix1325

    Mix1325 New Member

    Messages:
    9
    Hi Peter,
    A helper column with the purchase month is acceptable as well as Office 2016. The lookup for a specific purchase/prior month is not feasible because we are not sure if one existed and/or when exactly it took place.

    The result is used to assess whether a material was purchased prior to last month (max month in some cases will be the current month) and, if so, what was the cost associated with that previous purchase.
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    In that case the MAXIFS formula probably offers the most straightforward solution, returning either the final relevant transaction date or its month (characterised by the 1st day of the month) depending on what you need for the cost lookup.
    Mix1325 likes this.
  7. Mix1325

    Mix1325 New Member

    Messages:
    9
    I agree. Thank you so much for all your help in this matter.
    Peter Bartholomew likes this.

Share This Page