• 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...

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

Formula Question

Mix1325

New Member
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.
 
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 :)
 
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.
 

Attachments

  • Next to Last Month-Year Purchase.xlsx
    9.1 KB · Views: 5
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.
 

Attachments

  • Next to last Purchase Month (PB).xlsx
    10.8 KB · Views: 7
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.
 
A helper column with the purchase month is acceptable as well as office 2016
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.
 
Back
Top