Calculate difference b/w numbers

[Cobber2008]

Hello on the attached spreadsheet i want to for each particular row with the same Date and Time calculate the difference between each successive number based on the Rank (which is rank of Price lowest to highest). Ive calculated it for the first one manually to make it clear. So for the example with 5 rows dame date and time the difference between 6 ansd 5, 5 and 4, 4 and 3, 3 and 2, and 2 and 1. Thanks all.

Date	Rank	Time	Price	Seperation
1/04/2022​	1​	1:45:00 PM​	2.45​	0​
1/04/2022​	4​	1:45:00 PM​	10.5​	3.34​
1/04/2022​	5​	1:45:00 PM​	10.63​	0.13​
1/04/2022​	2​	1:45:00 PM​	3.93​	1.48​
1/04/2022​	3​	1:45:00 PM​	7.16​	3.23​
1/04/2022​	6​	1:45:00 PM​	30​	19.37​
1/04/2022​	1​	2:20:00 PM​	3.05​
1/04/2022​	3​	2:20:00 PM​	4.33​
1/04/2022​	4​	2:20:00 PM​	8.2​
1/04/2022​	2​	2:20:00 PM​	3.6​
1/04/2022​	5​	2:20:00 PM​	28.33​
1/04/2022​	1​	2:55:00 PM​	3.3​
1/04/2022​	2​	2:55:00 PM​	3.45​
1/04/2022​	7​	2:55:00 PM​	61.87​
1/04/2022​	3​	2:55:00 PM​	6​
1/04/2022​	5​	2:55:00 PM​	11​
1/04/2022​	6​	2:55:00 PM​	16.5​
1/04/2022​	4​	2:55:00 PM​	10.92​
1/04/2022​	1​	3:30:00 PM​	3.24​
1/04/2022​	7​	3:30:00 PM​	28.54​
1/04/2022​	3​	3:30:00 PM​	6.55​
1/04/2022​	2​	3:30:00 PM​	3.73​
1/04/2022​	6​	3:30:00 PM​	17​
1/04/2022​	5​	3:30:00 PM​	16.29​
1/04/2022​	4​	3:30:00 PM​	7.5​
1/04/2022​	1​	4:05:00 PM​	3.9​
1/04/2022​	5​	4:05:00 PM​	11.75​
1/04/2022​	7​	4:05:00 PM​	13.54​
1/04/2022​	6​	4:05:00 PM​	12.73​
1/04/2022​	8​	4:05:00 PM​	23.88​
1/04/2022​	3​	4:05:00 PM​	6.62​
1/04/2022​	4​	4:05:00 PM​	7.67​
1/04/2022​	9​	4:05:00 PM​	55.25​
1/04/2022​	2​	4:05:00 PM​	5.55​

 

[Peter_SSs]

Is this what you mean?

24 03 08.xlsm
	A	B	C	D	E
1	Date	Rank	Time	Price	Seperation
2	4/01/2022	1	1:45:00 PM	2.45	0
3	4/01/2022	4	1:45:00 PM	10.5	3.34
4	4/01/2022	5	1:45:00 PM	10.63	0.13
5	4/01/2022	2	1:45:00 PM	3.93	1.48
6	4/01/2022	3	1:45:00 PM	7.16	3.23
7	4/01/2022	6	1:45:00 PM	30	19.37
8	4/01/2022	1	2:20:00 PM	3.05	0
9	4/01/2022	3	2:20:00 PM	4.33	0.73
10	4/01/2022	4	2:20:00 PM	8.2	3.87
11	4/01/2022	2	2:20:00 PM	3.6	0.55
12	4/01/2022	5	2:20:00 PM	28.33	20.13
13	4/01/2022	1	2:55:00 PM	3.3	0
14	4/01/2022	2	2:55:00 PM	3.45	0.15
15	4/01/2022	7	2:55:00 PM	61.87	45.37
16	4/01/2022	3	2:55:00 PM	6	2.55
17	4/01/2022	5	2:55:00 PM	11	0.08
18	4/01/2022	6	2:55:00 PM	16.5	5.5
19	4/01/2022	4	2:55:00 PM	10.92	4.92
20	4/01/2022	1	3:30:00 PM	3.24	0
21	4/01/2022	7	3:30:00 PM	28.54	11.54
22	4/01/2022	3	3:30:00 PM	6.55	2.82
23	4/01/2022	2	3:30:00 PM	3.73	0.49
24	4/01/2022	6	3:30:00 PM	17	0.71
25	4/01/2022	5	3:30:00 PM	16.29	8.79
26	4/01/2022	4	3:30:00 PM	7.5	0.95
27	4/01/2022	1	4:05:00 PM	3.9	0
28	4/01/2022	5	4:05:00 PM	11.75	4.08
29	4/01/2022	7	4:05:00 PM	13.54	0.81
30	4/01/2022	6	4:05:00 PM	12.73	0.98
31	4/01/2022	8	4:05:00 PM	23.88	10.34
32	4/01/2022	3	4:05:00 PM	6.62	1.07
33	4/01/2022	4	4:05:00 PM	7.67	1.05
34	4/01/2022	9	4:05:00 PM	55.25	31.37
35	4/01/2022	2	4:05:00 PM	5.55	1.65
Diff

Cell Formulas
Range	Formula
E2:E35	E2	=D2-VLOOKUP(MAX(B2-1,1),FILTER(CHOOSECOLS(A$2:D$35,2,4),(A$2:A$35=A2)*(C$2:C$35=C2)),2,0)

 

[Cobber2008]

Is this what you mean?

Excellent thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.