Help with formula to capture a KPI

[Lukma]

Hi Friends

I need a help on how to Capt the set KPI which is provide i have my Tracking Sheet for each offload & Loading Time but due to Change KPI is set as Follow Below,

Average of Vessel time spent (Cargo Volume ≥ 70%) on Jack up rigs & Barges ≤ 20Hrs​

Average of Vessel time spent (Cargo Volume ≤ 69) on Jack up rigs & Barges ≤ 14Hrs​

Vessel time spent in day light operations on Complexes ≤ 12Hrs.​

I will Appreciate if any one could assist with below template.

Thanks

ILSP Vessels Offshore Tracking May-2022.xlsx
	B	C	D	E	F	G	H	I
2	Voyage	Vessel	Location	Deck %	Location Start Date & Time	Location End Date & Time	Total Hours
3	1037	ADNOC-221	Ettouk Island		04/26/22 10:20	04/26/22 23:00	12:40
4	1037	ADNOC-221	Ettouk Island		04/27/22 00:20	04/27/22 02:20	2:0
5	1037	ADNOC-221	Asseifiya Island		04/27/22 06:55	04/27/22 17:00	10:5
6	1038	ADNOC-811	UAP		04/27/22 08:55	04/27/22 09:05	0:10
7	1038	ADNOC-811	Barge Al Hyleh		04/27/22 09:50	04/27/22 13:10	3:20
8	1038	ADNOC-811	UAP		04/27/22 14:35	04/27/22 16:48	2:13
9	1038	ADNOC-811	UAP		04/27/22 16:38	04/27/22 17:05	0:27
10	1038	ADNOC-811	UAP		04/27/22 17:55	04/27/22 21:00	3:5
11	1038	ADNOC-811	Das Island		04/28/22 11:10	04/28/22 13:30	2:20
12	1039	Z-POWER	Al Ghallan Island		04/26/22 08:00	04/26/22 21:55	13:55
13	1040	ADNOC-1011	Umm Al Anbar		04/26/22 11:35	04/26/22 23:55	12:20
14	1040	ADNOC-1011	Ettouk Island		04/27/22 09:20	04/27/22 16:30	7:10
15	1040	ADNOC-1011	Asseifiya Island		04/27/22 21:30	04/27/22 22:45	1:15
16	1041	A-RADIANT-7	ACPT		04/26/22 07:45	04/26/22 11:10	3:25
17	1041	A-RADIANT-7	ACPT		04/27/22 07:20	04/27/22 14:20	7:0
18	1041	A-RADIANT-7	ZNSAT		04/27/22 16:00	04/27/22 17:20	1:20
19	1041	A-RADIANT-7	ACPT		04/27/22 20:05	04/27/22 20:45	0:40
20	1042	ADNOC-851	Arzanah Island		04/27/22 09:05	04/27/22 12:00	2:55
21	1043	ADNOC-950	Rig Al Bzoom		04/26/22 01:45	04/26/22 03:45	2:0
22	1043	ADNOC-950	Rig SMS Faith		04/26/22 07:30	04/26/22 19:45	12:15
Vessel Tracking

Cell Formulas
Range	Formula
H3:H22	H3	=G3-F3

 

[Ezguy4u]

Lukma, Key Performance Indicator, for marine shipping, can be kind of specialized. Some of us my need a little help. Anyway, the hot ticket is to show us input and output. So, I believe the above is the input. What we need now is the output. Give us an example of what you want to see for the output.

 

[Lukma]

Hi Ezguy4u

Sorry for the late reply i was trying to set my Data in Right way to show what i really need

i have created Two Data, one for the tracking from column B6 to H6 and From Column J7 to O7 will be my Unique extraction for Actual Time Spent for Each Location
But i need my Unique to be Dynamic in Column J7, K7 , L7 so as am Entry my in Column B7 to H it will be updating in Column J, K, & L

Then I need a formula O7 if Column M=K4 and L is less than 70 subtract 14 hrs and to give me the exceeded hours and if its Grater than 70 subtract 20 Hrs and IF column M=K5 Subtract 12 Hrs.

Appreciate if anyone could help out with the Fomula in Column O

Thank & Regards

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			KPI	Total Hours	Average
2			Jack Rigs 70%
3			Jack Rigs 69%								KPI Criteria		Jack	Jack	Complex
4			Complex 12 Hrs								Rigs&Barges	KPI	70%	69%
5											Complex	KPI Hours	20	14	12
6		MUS	Vessel	Location	Percentage	Location Start Date & Time	Location End Date & Time	Total Time		MUS	Unique Location	Unique Pecentage	Rig / Complex	Total Hour	Exceed KPI Time
7	1383 Rig Muhaiyimat	1383	QMS CARDINAL	Rig Muhaiyimat	10%	06/08/22 05:00	06/08/22 05:30	0:30		1383	Rig Muhaiyimat	10%	Rigs&Barges	11:30
8	1383 Rig Al Lulu	1383	QMS CARDINAL	Rig Al Lulu	75%	06/08/22 05:30	06/08/22 23:00	17:30		1383	Rig Al Lulu	75%	Rigs&Barges	26:30	6:30
9	1383 Rig Muhaiyimat	1383	QMS CARDINAL	Rig Muhaiyimat	10%	06/09/22 07:00	06/09/22 18:00	11:00		1384	Rig Al Bzoom	10%	Rigs&Barges	1:30
10	1383 Rig Al Lulu	1383	QMS CARDINAL	Rig Al Lulu	75%	06/10/22 08:00	06/10/22 17:00	9:00		1384	Barge Pride	35%	Rigs&Barges	5:20
11	1384 Rig Al Bzoom	1384	A-GRACE	Rig Al Bzoom	10%	06/08/22 20:00	06/08/22 21:30	1:30		1384	Barge Shahama	35%	Rigs&Barges	6:55
12	1384 Barge Pride	1384	A-GRACE	Barge Pride	35%	06/09/22 02:20	06/09/22 07:40	5:20		1384	Rig Diyina	20%	Rigs&Barges	6:55
13	1384 Barge Shahama	1384	A-GRACE	Barge Shahama	35%	06/09/22 10:00	06/09/22 16:55	6:55		1384	Rig Junana		Rigs&Barges	1:35
14	1384 Rig Diyina	1384	A-GRACE	Rig Diyina	20%	06/09/22 19:45	06/10/22 02:40	6:55		1385	Bu Sikeen Island	70%	Drl-Island	7:45
15	1384 Rig Junana	1384	A-GRACE	Rig Junana		06/10/22 07:50	06/10/22 09:25	1:35		1385	Al Qatia Island	30%	Drl-Island	2:5
16	1385 Bu Sikeen Island	1385	ADNOC-223	Bu Sikeen Island	70%	06/09/22 06:40	06/09/22 09:15	2:35		1386	ACPT	80%	Complex	7:10
17	1385 Bu Sikeen Island	1385	ADNOC-223	Bu Sikeen Island	70%	06/09/22 14:30	06/09/22 14:50	0:20		1386	Barge Constructor		Complex	1:45
18	1385 Bu Sikeen Island	1385	ADNOC-223	Bu Sikeen Island	70%	06/09/22 15:30	06/09/22 19:10	3:40		1386	ZWSAT		Drl-Island	2:20
19	1385 Al Qatia Island	1385	ADNOC-223	Al Qatia Island	30%	06/09/22 21:25	06/09/22 23:30	2:05		1386	Barge Leen	20%	Complex	6:10
20	1385 Bu Sikeen Island	1385	ADNOC-223	Bu Sikeen Island	70%	06/10/22 06:30	06/10/22 07:40	1:10		1386	Al Ghallan Island		Drl-Island	0:25
21	1386 ACPT	1386	ADNOC-851	ACPT	80%	06/08/22 10:40	06/08/22 11:20	0:40		1387	Rig SMS Faith	25%	Rigs&Barges	25:12	11:12
22	1386 ACPT	1386	ADNOC-851	ACPT	80%	06/08/22 13:55	06/08/22 17:10	3:15		1387	Rig Al Ittihad	25%	Rigs&Barges	5:50
23	1386 ACPT	1386	ADNOC-851	ACPT	80%	06/08/22 17:45	06/08/22 18:00	0:15		1387	Rig Ariabahatt-1	15%	Rigs&Barges	2:18
24	1386 ACPT	1386	ADNOC-851	ACPT	80%	06/08/22 19:20	06/08/22 19:55	0:35		1387	Rig Al Yasat	15%	Rigs&Barges	18:0
25	1386 ACPT	1386	ADNOC-851	ACPT	80%	06/08/22 21:35	06/09/22 00:00	2:25
26	1386 Barge Constructor	1386	ADNOC-851	Barge Constructor		06/09/22 00:55	06/09/22 02:40	1:45
27	1386 ZWSAT	1386	ADNOC-851	ZWSAT		06/09/22 07:40	06/09/22 10:00	2:20
28	1386 Barge Leen	1386	ADNOC-851	Barge Leen	20%	06/09/22 13:10	06/09/22 19:20	6:10
29	1386 Al Ghallan Island	1386	ADNOC-851	Al Ghallan Island		06/10/22 00:35	06/10/22 01:00	0:25
30	1387 Rig SMS Faith	1387	ADNOC-222	Rig SMS Faith	25%	06/08/22 11:24	06/08/22 12:00	0:36
31	1387 Rig Al Ittihad	1387	ADNOC-222	Rig Al Ittihad	25%	06/08/22 13:10	06/08/22 19:00	5:50
32	1387 Rig Ariabahatt-1	1387	ADNOC-222	Rig Ariabahatt-1	15%	06/08/22 23:54	06/09/22 02:12	2:18
33	1387 Rig Al Yasat	1387	ADNOC-222	Rig Al Yasat	15%	06/09/22 08:40	06/09/22 17:40	9:00
34	1387 Rig SMS Faith	1387	ADNOC-222	Rig SMS Faith	25%	06/09/22 11:24	06/10/22 12:00	24:36
35	1387 Rig Al Yasat	1387	ADNOC-222	Rig Al Yasat	15%	06/09/22 08:40	06/09/22 17:40	9:00
Data

Cell Formulas
Range	Formula
J7:L24	J7	=UNIQUE(CHOOSE({1,3,4},B7:B34,C7:C34,D7:D34,E7:E34))
O8	O8	=N8-M5
E7:E9,E31:E33,E11:E29	E7	=IFERROR(VLOOKUP(A7,'Rig_%'!$C$4:$F$48,4,0),"")
O21	O21	=N21-N5
N7:N24	N7	=IFERROR(SUMIFS($H$7:$H$35,$D$7:$D$35,K7,$B$7:$B$35,J7),"")
H7:H34	H7	=G7-F7
A7:A35	A7	=B7&" "&D7
K25:K35	K25	=IFERROR(VLOOKUP(L25,'Rig_%'!$L$4:$M$21,2,0),"")
M7:M24	M7	=IFERROR(VLOOKUP(K7,'Rig_%'!$L$4:$M$22,2,0),"")
M25:M35	M25	=IFERROR(VLOOKUP(K25,'Rig_%'!$L$4:$M$21,2,0),"")
Dynamic array formulas.