Calculation with changes in time. Update.

[Davebro]

I am trying to find a way to use the Rank in column E to work when the calculation is carried down to the different times

Time	B	C	Calc	Rank
15:35:00​	131​	105​	-4​	9​
15:35:00​	128​	89​	9​	3​
15:35:00​	128​	0​	98​	1​
15:35:00​	128​	93​	5​	4​
15:35:00​	128​	89​	9​	2​
15:35:00​	128​	89​	9​	2​
15:35:00​	128​	97​	1​	3​
15:35:00​	128​	95​	3​	2​
15:35:00​	128​	74​	24​	1​
19:30:00​	128​	97​
19:30:00​	128​	0​
19:30:00​	128​	0​
19:30:00​	128​	99​
19:30:00​	128​	96​
19:30:00​	128​	0​
19:30:00​	128​	99​
19:30:00​	128​	0​
19:30:00​	128​	95​
19:30:00​	128​	0​
19:30:00​	128​	0​
19:30:00​	128​	95​
20:20:00​	135​	50​
20:20:00​	135​	47​
20:20:00​	135​	50​
20:20:00​	135​	42​
20:20:00​	135​	50​
20:20:00​	125​	44​
20:20:00​	125​	48​

 

[myall_blues]

You need to provide us with some additional information to work with. For example:

• Is the rank value a formula? If yes what is it?
• As above for the calc value, B and C.
• Show an example of your desired result.

Ideally if you could post some data as a minisheet using XL2BB.

 

[Davebro]

You need to provide us with some additional information to work with. For example:

• Is the rank value a formula? If yes what is it?
• As above for the calc value, B and C.
• Show an example of your desired result.

Ideally if you could post some data as a minisheet using XL2BB.

Sorry for being vague, I am trying to carry the formulas down to adjust to the next times in A

Book2a.xlsx A B C D E F G H I J K L 1 Time B C Calc Rank 2 15:35:00 131 105 0 1 D =(MAX($C$2:$C$10) - C2) - (MAX($B$2:$B$10) - B 3 15:35:00 128 89 13 6 E =RANK(D2,$D$2:$D$10,1) 4 15:35:00 128 99 3 2 5 15:35:00 128 93 9 5 6 15:35:00 129 89 14 8 7 15:35:00 128 89 13 6 8 15:35:00 128 97 5 3 9 15:35:00 128 95 7 4 10 15:35:00 128 74 28 9 11 19:30:00 128 97 12 19:30:00 128 0 13 19:30:00 128 0 14 19:30:00 128 99 15 19:30:00 128 96 16 19:30:00 128 0 17 19:30:00 128 99 18 19:30:00 128 0 19 19:30:00 128 95 20 19:30:00 128 0 21 19:30:00 128 0 22 19:30:00 128 95 23 20:20:00 135 50 24 20:20:00 135 47 25 20:20:00 135 50 26 20:20:00 135 42 27 20:20:00 135 50 28 20:20:00 125 44 29 20:20:00 125 48 Sheet1 Cell Formulas Range Formula D2:D10 D2 =(MAX($C$2:$C$10) - C2) - (MAX($B$2:$B$10) - B2) E2:E10 E2 =RANK(D2,$D$2:$D$10,1)

 

[myall_blues]

See formulas in columns G and H

junk.xlsx
	A	B	C	D	E	F	G	H
1	Time	B	C	Calc	Rank
2	3:35:00 PM	131	105	0	1		0	1
3	3:35:00 PM	128	89	13	6		13	6
4	3:35:00 PM	128	99	3	2		3	2
5	3:35:00 PM	128	93	9	5		9	5
6	3:35:00 PM	129	89	14	8		14	8
7	3:35:00 PM	128	89	13	6		13	6
8	3:35:00 PM	128	97	5	3		5	3
9	3:35:00 PM	128	95	7	4		7	4
10	3:35:00 PM	128	74	28	9		28	9
11	7:30:00 PM	128	97	2	3		2	3
12	7:30:00 PM	128	0	99	7		99	7
13	7:30:00 PM	128	0	99	7		99	7
14	7:30:00 PM	128	99	0	1		0	1
15	7:30:00 PM	128	96	3	4		3	4
16	7:30:00 PM	128	0	99	7		99	7
17	7:30:00 PM	128	99	0	1		0	1
18	7:30:00 PM	128	0	99	7		99	7
19	7:30:00 PM	128	95	4	5		4	5
20	7:30:00 PM	128	0	99	7		99	7
21	7:30:00 PM	128	0	99	7		99	7
22	7:30:00 PM	128	95	4	5		4	5
23	8:20:00 PM	135	50	0	3		0	3
24	8:20:00 PM	135	47	3	6		3	6
25	8:20:00 PM	135	50	0	3		0	3
26	8:20:00 PM	135	42	8	7		8	7
27	8:20:00 PM	135	50	0	3		0	3
28	8:20:00 PM	125	44	-4	2		-4	2
29	8:20:00 PM	125	48	-8	1		-8	1
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=(MAX($C$2:$C$10) - C2) - (MAX($B$2:$B$10) - B2)
E2:E10	E2	=RANK(D2,$D$2:$D$10,1)
D11:D22	D11	=(MAX($C$11:$C$22) - C11) - (MAX($B$11:$B$22) - B11)
E11:E22	E11	=RANK(D11,$D$11:$D$22,1)
D23:D29	D23	=(MAX($C$23:$C$29) - C23) - (MAX($B$23:$B$29) - B23)
E23:E29	E23	=RANK(D23,$D$23:$D$29,1)
G2:G29	G2	=(MAXIFS($C$2:$C$29,$A$2:$A$29,A2)-C2)-(MAXIFS($B$2:$B$29,$A$2:$A$29,A2)-B2)
H2:H29	H2	=COUNTIFS($A$2:$A$29,A2,$D$2:$D$29,"<"&D2)+1

 

[Davebro]

See formulas in columns G and H

junk.xlsx
	A	B	C	D	E	F	G	H
1	Time	B	C	Calc	Rank
2	3:35:00 PM	131	105	0	1		0	1
3	3:35:00 PM	128	89	13	6		13	6
4	3:35:00 PM	128	99	3	2		3	2
5	3:35:00 PM	128	93	9	5		9	5
6	3:35:00 PM	129	89	14	8		14	8
7	3:35:00 PM	128	89	13	6		13	6
8	3:35:00 PM	128	97	5	3		5	3
9	3:35:00 PM	128	95	7	4		7	4
10	3:35:00 PM	128	74	28	9		28	9
11	7:30:00 PM	128	97	2	3		2	3
12	7:30:00 PM	128	0	99	7		99	7
13	7:30:00 PM	128	0	99	7		99	7
14	7:30:00 PM	128	99	0	1		0	1
15	7:30:00 PM	128	96	3	4		3	4
16	7:30:00 PM	128	0	99	7		99	7
17	7:30:00 PM	128	99	0	1		0	1
18	7:30:00 PM	128	0	99	7		99	7
19	7:30:00 PM	128	95	4	5		4	5
20	7:30:00 PM	128	0	99	7		99	7
21	7:30:00 PM	128	0	99	7		99	7
22	7:30:00 PM	128	95	4	5		4	5
23	8:20:00 PM	135	50	0	3		0	3
24	8:20:00 PM	135	47	3	6		3	6
25	8:20:00 PM	135	50	0	3		0	3
26	8:20:00 PM	135	42	8	7		8	7
27	8:20:00 PM	135	50	0	3		0	3
28	8:20:00 PM	125	44	-4	2		-4	2
29	8:20:00 PM	125	48	-8	1		-8	1
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=(MAX($C$2:$C$10) - C2) - (MAX($B$2:$B$10) - B2)
E2:E10	E2	=RANK(D2,$D$2:$D$10,1)
D11:D22	D11	=(MAX($C$11:$C$22) - C11) - (MAX($B$11:$B$22) - B11)
E11:E22	E11	=RANK(D11,$D$11:$D$22,1)
D23:D29	D23	=(MAX($C$23:$C$29) - C23) - (MAX($B$23:$B$29) - B23)
E23:E29	E23	=RANK(D23,$D$23:$D$29,1)
G2:G29	G2	=(MAXIFS($C$2:$C$29,$A$2:$A$29,A2)-C2)-(MAXIFS($B$2:$B$29,$A$2:$A$29,A2)-B2)
H2:H29	H2	=COUNTIFS($A$2:$A$29,A2,$D$2:$D$29,"<"&D2)+1

Many thanks for you time and solution, this works fine, I did some combinations with countifs but could not get it right. Much appreciated.