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

In this post, we will see how to resolve How to return the last n number of values corresponding to a specific category?

Question:

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.

Best Answer:

You can try:

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.

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Question:

Best Answer:

Related