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

0 votes

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 dynamic formula can I use to retrieve the most recent 5 prices for category BASE Y-20? The difficult element is that, if 5 values are missing, the formula must return any prices that are available. (For example, The values 270.06, 270, 270.48, 272.88, and 276.5 must be returned for the given data. If we only had the first row, it would have to return 279)

I used some products. Naturally, that provides the corresponding prices. To obtain the last 5 data, offset is available. But there is no way to see the most recent dynamically updated prices for the top 5 categories.

Feb 5, 2023 in Others by Kithuzzz
• 38,010 points
283 views

1 answer to this question.

0 votes

Try:

enter image description here

The formula in F3:

=TAKE(SORT(FILTER(A:C,B:B=F1),1),-F2,-1)
answered Feb 5, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
392 views
0 votes
1 answer
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
520 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,440 points
1,622 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,564 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,690 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
832 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,035 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,720 points
870 views
0 votes
1 answer

how to show the <meta> values of yoast seo with some function?

check this folder : wordpress-seo\src\presenters\open-graph you can ...READ MORE

answered Feb 10, 2022 in Others by narikkadan
• 63,720 points
460 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP