Formula to check if a later purchase has a better/ worse price

[qetuo]

Dear Excel Overlords,

I am becoming desperate with what appears to be a easy problem but after trying for hours I still can not get the correct output.
I know I could just sort my list to some convenient order and probably get my expected outcome but then as soon as I change the order it will mess up the output.

The Case: I purchase various items at different times for different prices. Now I just want to know if my purchases are improving (lower price over time) or worsening (higher price over time).

For Example:

Date	ID	Price
10/02/21	B	99
03/04/21	A	12
04/08/21	C	88
03/06/21	A	13
10/02/21	A	14
21/01/21	C	77

I bought product A three times, now I want to know if the highest price (14, which I can find with "MAXIFS") is also the first purchase. And vice-versa the lowest price (12, "MINIFS") in the best case szenario the last purchase. Next to my list of all purchases I used to UNIQUE Formula to get a list and then countif/averageif/minif/maxif to do some analysis. What I was trying to do is another column with basically some formula with two conditions: 1. ID = A and 2. smallest/biggest date and then give me the output price. With this result I could do some simple comparison, like "IF min>max" or something like that.

Another idea was to have another column next to all purchases to check if there was a previous purchase and if yes if it increased or decreases, but I could not wrap my head around this either.

I am not sure if I am missing the forest through the trees or if this actually requires some complicated formula.

Any help is appreciated.

 

[JamesCanale]

Here are some things you can do:

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	ID	Price			Min Paid	Max Paid	First Paid	Most Recent
2	2/10/2021	B	99		A	12	14	14	13
3	4/3/2021	A	12		B	99	99	99	99
4	8/4/2021	C	88		C	77	88	77	88
5	6/3/2021	A	13
6	2/10/2021	A	14
7	1/21/2021	C	77
Sheet21

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(B2:B7))
F2:F4	F2	=MIN(INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7=E2),1),,3))
G2:G4	G2	=MAX(INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7=E2),1),,3))
H2:H4	H2	=INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7=E2),1),1,3)
I2:I4	I2	=INDEX(SORT(FILTER($A$2:$C$7,$B$2:$B$7=E2),1),ROWS(FILTER($A$2:$C$7,$B$2:$B$7=E2)),3)
Dynamic array formulas.

 

[qetuo]

Thanks. This helps me a lot. Especially the Filter and Sort Formula, no idea why I didn't think about that.