Find the date that corresponds to the lowest price in the last X months

[JenniferMurphy]

I have a workbook with records of items I buy regularly. Each product has its own sheet. There is one row for each purchase with columns for Date, Quantity, Price, Price per pound, etc.

Each sheet has a cell showing the lowest price (BestPrice) I ever paid and another showing the date of that purchase.

The named ranges are:

• RangeDate: The list of purchase dates
• RangePrice: The list or prices paid

The lowest price and the date of that purchase are obtained with these expressions:

=MIN(RangePrice)
=INDEX(RangeDate,MATCH(BestPrice,RangePrice,0))

That all works. But some of these sheets have records going back many years. If the lowest price was 15 years ago, it's not really a fair comparison to today's prices.

Here's my question: How can I find the date of the lowest price paid in the last X years without writing a UDF?

 

[alansidman]

Why not just filter out all dates greater than 15 years and work with that data. Once filtered, move the remaining data to a new sheet/file.

 

[JenniferMurphy]

Why not just filter out all dates greater than 15 years and work with that data. Once filtered, move the remaining data to a new sheet/file.

Several reasons:

• I may want to change the time period and I don't want to have redo the extraction process.
• I don't want to have to redo the extraction process every year or so when some records are now older than X years.

I want a dynamic result that works on all of the data.

 

[Ron Coderre]

Try something like this:

Enter a minimum date in a cell and name it MinDate
Name appropriate cells:

MinDate      =a minimum date to consider: eg 2018-01-01
MinPrice     =MINIFS(RangePrice,RangeDate,">="&MinDate)
MatchDate    =MAXIFS(RangeDate,RangeDate,">="&MinDate,RangePrice,MinPrice)

Is that something you can work with?

 

[JenniferMurphy]

Try something like this:

Enter a minimum date in a cell and name it MinDate
Name appropriate cells:

MinDate      =a minimum date to consider: eg 2018-01-01
MinPrice     =MINIFS(RangePrice,RangeDate,">="&MinDate)
MatchDate    =MAXIFS(RangeDate,RangeDate,">="&MinDate,RangePrice,MinPrice)

Is that something you can work with?

Thank. ?? Sorry for the very late reply. ??