Search code examples
excelexcel-formuladata-analysis

How to return the last n number of values corresponding to a specific category?

I have the following sample data.

Date         Category   Price   Quantity
02-01-2019  BASE_Y-20   279 1
02-01-2019  BASE_Y-21   271.25  0
03-01-2019  BASE_Y-20   276.5   2
03-01-2019  BASE_Y-21   266.5   0
04-01-2019  BASE_Y-20   272.88  14
04-01-2019  BASE_Y-21   266.5   1
07-01-2019  BASE_Y-20   270.48  29
07-01-2019  BASE_Y-21   262.75  0
08-01-2019  BASE_Y-20   270 4
08-01-2019  BASE_Y-21   264 0
09-01-2019  BASE_Y-20   270.06  31
09-01-2019  BASE_Y-21   262.85  0

What is a dynamic formula that I can use to return the last 5 prices corresponding to category BASE_Y-20 ? The formula must return whatsoever prices are available, if 5 values are not present, which is the challenging part. (Eg: For the given data, 270.06, 270, 270.48, 272.88 and 276.5 must be returned. If we only had 1st row, it must return 279)

I have tried sumproduct. That of course gives the corresponding prices. Offset can be availed to get last 5 data. But no way for getting last 5 prices corresponding to a specific category that is dynamic.

Solution

  • You can try:

    enter image description here

    Formula in F3:

    =TAKE(SORT(FILTER(A:C,B:B=F1),1),-F2,-1)

    Few notes:

    • The latest price will be at the bottom;
    • If your data is always sorted to begin with, just ditch the nested SORT() and use =TAKE(FILTER(A:C,B:B=F1),-F2,-1);
    • If no value is present at all, nest the formula in an =IFERROR(<Formula>,"") to return any value you'd like to display in such event.