Help With Correction of Formula in my Data.

[Lukma]

Hi Friends

Please i need a help with my formula in Cell D 4, 5, 6, 7, 8
my objective is to count base on Multiple criteria so
1. Count Cell Q base on Multiple Range O & N & K and Criteria But my Problem is that in the Range N is base on IF less that or equal to 69% and the other is If greater or quall to 70% i was able to tray but i think am missing out something Please if any one could help to Check all my formula and to assist me with it.

Thanks

ILSP Offshore Vessel Tracking Time Spent-2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		ILSP	KPI	No's of Visit	Total Hours	Average hrs.
4	Rigs&Barges	JackRigs	70%	16	269:35	16:50
5	Rigs&Barges	JackRigs	69%	44	302:30	6:52
6	Complex	Complex	12Hrs	22	143:31	6:31
7	Drl-Island	Drl-Island	10 Hrs	33	175:50	5:19
8	Prd-Island	Prd-Island	10 Hrs	5	7:35	1:31
9		June	70%	69%
10		ILSP KPI	20	14	12	10
11		Month	Voyage	Vessel	Location	Deck Load %	Location Start Date & Time	Location End Date & Time	Total Time		Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent	Exceed KPI
12	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 07:10	27/04/22 08:02	0:52		April	1044	ABK Complex	80%	Complex	26:46	14:46
13	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 09:40	27/04/22 09:48	0:08		May	1044	ABK Complex	80%	Complex	26:46	14:46
14	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 07:10	28/04/22 07:52	0:42		May	1044	Mutawa-202	0%		0:15
15	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 08:15	28/04/22 08:23	0:08		April	1045	Rig Vivekanand-3	100%	Rigs&Barges	63:00	43:00
16	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 06:50	01/05/22 07:07	0:17		April	1045	Rig Yemillah	0%	Rigs&Barges	12:10
17	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 08:25	02/05/22 08:33	24:08		April	1046	Nasr Complex	0%		10:15
18	1044 Mutawa-202	May	1044	Z-QUEEN	Mutawa-202	0%	01/05/22 14:30	01/05/22 14:45	0:15		April	1046	Barge Pepper	20%	Prd-Barge	2:30
19	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 08:55	02/05/22 09:14	0:19		April	1047	Al Qatia Island	50%	Drl-Island	2:34
20	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 10:40	02/05/22 10:52	0:12		April	1047	Bu Sikeen Island	20%	Drl-Island	0:33
21	1045 Rig Vivekanand-3	April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	27/04/22 15:30	27/04/22 20:10	4:40		April	1048	Al Ghallan Island	10%	Drl-Island	0:20
22	1045 Rig Yemillah	April	1045	QMS MARIMBA	Rig Yemillah	0%	27/04/22 22:20	28/04/22 10:30	12:10		April	1048	Umm Al Anbar	30%	Drl-Island	10:50	0:50
23	1045 Rig Vivekanand-3	April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	28/04/22 12:00	30/04/22 22:20	58:20		April	1048	Asseifiya Island	10%	Drl-Island	5:30
24	1046 Nasr Complex	April	1046	ADNOC-810	Nasr Complex	0%	27/04/22 08:00	27/04/22 18:15	10:15		April	1048	Ettouk Island	60%	Drl-Island	8:35
25	1046 Barge Pepper	April	1046	ADNOC-810	Barge Pepper	20%	27/04/22 21:30	28/04/22 00:00	2:30		April	1049	Rig Junana	0%	Rigs&Barges	0:05
26	1047 Al Qatia Island	April	1047	LCT-WARDEH	Al Qatia Island	50%	28/04/22 02:00	28/04/22 02:40	0:40		April	1049	Rig SMS Esse	0%	Rigs&Barges	19:15
ILSP_Offshore_Vessel_Tracking

Cell Formulas
Range	Formula
D4	D4	=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9,$N$12:$N$900000,">=70%")
E4	E4	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,">=70%")
F4:F8	F4	=E4/D4
D5	D5	=COUNTIFS($Q$12:$Q$900000,">=0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,"<=69%")
E5	E5	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,"<=69%")
D6	D6	=COUNTIFS($O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E6	E6	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9)
D7	D7	=COUNTIFS($O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E7	E7	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9)
D8	D8	=COUNTIFS($O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E8	E8	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9)
A12:A26	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N1739	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
O12:O26	O12	=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P26	P12	=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q26	Q12	=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))
F12:F26	F12	=IFERROR(VLOOKUP(A12,Info_Setting!$P$4:$S$600000,4,0),"0%")
I12:I26	I12	=IFERROR(1/(1/SUM([@[Location End Date & Time ]]-[@[Location Start Date & Time ]])),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=Info_Setting!$K$3:$K$15

 

[Lukma]

I see that you posted a near duplicate request here:

Need Help With a Formula reposting my Data

Hi friends I need you help with my spreadsheet as am reporting this I have a KPI set for Jackup-Rigs/Barges in two ways 1. >=70% deck load on any Location show spend

www.mrexcel.com

...which can add to confusion and duplication of effort.

Here is my best guess for what you want, but you haven't answered questions I asked earlier that would address some of the uncertainty. Also note that the yellow cell (Prd-Barge) will never match anything in the lookup table (see my earlier questions about this)...for now, I have no matches return a KPI of 0 hours, so nothing is subtracted from the Actual Time Spent.

MrExcel_20220715b.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		ILSP	Deck Load Threshold	KPI (h)	No's of Visit	Total Hours	Average Hours
4	Rigs&Barges	JackRigs	>=70%	20	1	43	43
5	Rigs&Barges	JackRigs	<=69%	14	0	0	N/A
6	Complex	Complex	N/A	12	1	14.7666667	14.7666667
7	Drl-Island	Drl-Island	N/A	10	0	0	N/A
8	Prd-Island	Prd-Island	N/A	10	0	0	N/A
9		April-22
10
11		Month	Voyage	Vessel	Location	Deck Load %	Location Start Date & Time	Location End Date & Time	Total Time		Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent (d)	Exceed KPI (h)
12	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.2986	44678.3347	0.0361111		Apr-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
13	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.4028	44678.4083	0.0055556		May-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
14	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.2986	44679.3278	0.0291667		May-22	1044	Mutawa-202	0%		0.01041667	0.25
15	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.3438	44679.3493	0.0055556		Apr-22	1045	Rig Vivekanand-3	100%	Rigs&Barges	2.625	43
16	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.2847	44682.2965	0.0118056		Apr-22	1045	Rig Yemillah	0%	Rigs&Barges	0.50694444	0
17	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.3507	44683.3563	1.0055556		Apr-22	1046	Nasr Complex	0%		0.42708333	10.25
18	1044 Mutawa-202	May-22	1044	Z-QUEEN	Mutawa-202	0%	44682.6042	44682.6146	0.0104167		Apr-22	1046	Barge Pepper	20%	Prd-Barge	0.10416667	2.5
19	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.3715	44683.3847	0.0131944		Apr-22	1047	Al Qatia Island	50%	Drl-Island	0.02777778	0
20	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.4444	44683.4528	0.0083333		Jan-00	0	0	0%		0	0
21	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44678.6458	44678.8403	0.1944444
22	1045 Rig Yemillah	Apr-22	1045	QMS MARIMBA	Rig Yemillah	0%	44678.9306	44679.4375	0.5069444
23	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44679.5	44681.9306	2.4305556
24	1046 Nasr Complex	Apr-22	1046	ADNOC-810	Nasr Complex	0%	44678.3333	44678.7604	0.4270833
25	1046 Barge Pepper	Apr-22	1046	ADNOC-810	Barge Pepper	20%	44678.8958	44679	0.1041667
26	1047 Al Qatia Island	Apr-22	1047	LCT-WARDEH	Al Qatia Island	50%	44679.0833	44679.1111	0.0277778
Sheet9

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$Q$12:$Q$900000,">0",$N$12:$N$900000,">=70%")
F4	F4	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$N$12:$N$900000,">=70%")
G4:G8	G4	=IFERROR(F4/E4,"N/A")
E5	E5	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$Q$12:$Q$900000,">0",$N$12:$N$900000,"<=69%")
F5	F5	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$N$12:$N$900000,"<=69%")
E6:E8	E6	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A6,$Q$12:$Q$900000,">0")
F6:F8	F6	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A6)
A12:A26	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N20	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
P12:P20	P12	=SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)
Q12:Q20	Q12	=IF(AND(O12="Rigs&Barges",N12>0.69,N12<0.7),"undefined",MAX(0,P12*24-IF(O12="Rigs&Barges",(N12>=0.7)*$D$4 + (N12<=0.69)*$D$5, IFERROR(INDEX($D$6:$D$8,MATCH(O12,$A$6:$A$8,0)),0))))
O14,O20:O26,O17	O14	=IFERROR(VLOOKUP(M14,Location,2,0),"")
I12:I26	I12	=[@[Location End Date & Time ]]-[@[Location Start Date & Time ]]
Dynamic array formulas.

Krice

Thank you, however like i said this is the KPI

[

i also explained as below though your Question i might not be able to answer correctly. anyhow i will have to do it manually for the time been, for now i will explain what i required If you can help out

In Column Q12 i need the formula =IF Column N>=70% and O=Rigs&Barges and P>=20hrs then Subtract P-20hrs, and IF Column N<=69% and O=Rigs&Barges and P>=14hrs then Subtract P-14hrs and IF O=Complex and P>12Hrs the Subtract P-12hrs, and IF O=Prd-Island and P>10Hrs the Subtract P-10hrs and IF O=Drd-Island and P>10Hrs the Subtract P-10hrs and IF O is Blank then do Nothing

This is best i could explain what i need the formula in Column Q

Thanks once again

 

[KRice]

In Column Q12 i need the formula =IF Column N>=70% and O=Rigs&Barges and P>=20hrs then Subtract P-20hrs, and IF Column N<=69% and O=Rigs&Barges and P>=14hrs then Subtract P-14hrs and IF O=Complex and P>12Hrs the Subtract P-12hrs, and IF O=Prd-Island and P>10Hrs the Subtract P-10hrs and IF O=Drd-Island and P>10Hrs the Subtract P-10hrs and IF O is Blank then do Nothing

The formula I posted in Q12 (and down) does this. You, however, never answered my question about a blank in column O. As I mentioned, I assumed a 0 would be returned, so effectively nothing is done to the value in column P when the corresponding O column is blank. The formula in column Q could possibly be simplified further if you would answer my earlier question about whether a Deck % between 69% and 70% is possible. Right now, the first part of the formula checks for that condition.

You also have not commented on my question about the "Prd-Barge" entry, which produces no matches...so I have no idea what the expected result should be.

 

[Lukma]

Hi krice

From the question you have ask me is that the Rigs&Barges are location sites and apparently location are meant to spent specific time for each deck Percentage
Which means from 0% to 69% should be 14 hours Only so if it exceed 14hrs then it should subtract P from 14hrs to give me the exceed Hours in Column Q
Same thing goes for 70% to 100% deck should spend 20hours and once time spent exceed 20hrs it should subtract column P from 20 hrs and result should be in Column Q
for the Complex there is no Deck Percentage specify only Time Spent if it Exceed 12 hours then it should Subtract P from 12 hrs
Same Goes for Prd-Island & Drl-Island there is no Deck Percentage just Time spent and once it exceed 10 hours. it should subtract column P from 10 hours

However i will provide you with previous sample which Half deck & Full Deck before the KPI was change to Percentage. I believe from here you might understand thanks

2022 IPP Planning Supply Chain Tracking DATA.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6		June	ILSP Performance Leads		Total Hrs	No's of Visit	Avrg Hrs.
7	Rigs&Barges	Avrg Time Full Deck Jackup Rigs <20			152:23	10	15:14
8	Rigs&Barges	Avrg Time Half Deck Jackup Rigs <14			641:50	70	9:10
9	Drl-Island	Avrg Time Drilling Island <10			175:5	31	5:38
10	Prd-Island	Avrg Time Dlight Prd-Island Rigs <10			4:35	1	4:35
11	Prd-Complex	Avrg Time Daylight Complex <12			143:56	23	6:15
12		KPI Control
13	P	ILSP KPI	20	14	12	10	Full
14	D	Month	Mus-No	Supply Vessel	VSL D/P	Dep ADL&S Date & Time	Half	Site	Date & Time	Date & Time	Spent Hrs		KPI Structure	Location Site's	Total Hrs Spent	Exceed KPI Hrs
15	1Rig Marawwah	January	1	SEACOR PARANA	D	02/01/22 11:00	Half	Rig Marawwah	03/01/22 23:15	04/01/22 03:50	4:35		Rigs&Barges	Rig Marawwah	0.21
16	1Rig Marawwah	January	1	SEACOR PARANA	D	02/01/22 11:00	Half	Rig Marawwah	04/01/22 04:55	04/01/22 05:28	0:33
17	2ACPT	January	2	ADNOC-850	P	03/01/22 03:00	Full	ACPT	03/01/22 13:00	03/01/22 19:20	6:20		Prd-Complex	ACPT	0.26
18	2ADNOC-710	January	2	ADNOC-850	P	03/01/22 03:00	Full	ADNOC-710	03/01/22 21:15	03/01/22 22:15	1:00			ADNOC-710	0.04
19	3Rig Qarnin	January	3	ADNOC-S01	D	01/01/22 18:10	Half	Rig Qarnin	03/01/22 07:36	03/01/22 20:00	12:24		Rigs&Barges	Rig Qarnin	0.52
20	4Rig Junana	January	4	M-SUPPORTER	D	02/01/22 13:10	Full	Rig Junana	03/01/22 02:00	03/01/22 03:25	1:25		Rigs&Barges	Rig Junana	0.50
21	4Rig Junana	January	4	M-SUPPORTER	D	02/01/22 13:10	Full	Rig Junana	03/01/22 06:50	03/01/22 17:30	10:40
22	4Barge Pride	January	4	M-SUPPORTER	D	02/01/22 13:10	Full	Barge Pride	03/01/22 19:10	03/01/22 22:15	3:05		Rigs&Barges	Barge Pride	0.13
23	4Rig Ariabahatt-1	January	4	M-SUPPORTER	D	02/01/22 13:10	Full	Rig Ariabahatt-1	04/01/22 14:00	04/01/22 22:00	8:00		Rigs&Barges	Rig Ariabahatt-1	0.33
24	5Al Ghallan Island	January	5	ADNOC-221	D	02/01/22 10:40	Full	Al Ghallan Island	02/01/22 22:35	03/01/22 20:00	21:25		Drl-Island	Al Ghallan Island	0.89	0.48
ILSP Vessel Activity Tracking

Cell Formulas
Range	Formula
E7:E8	E7	=SUMIFS(ExceedKPI[Exceed KPI Hrs],ExceedKPI[KPI Structure],$A7,ILSP[Half],$G13,ILSP[Month],$B$6)
F7:F8	F7	=IFERROR(COUNTIFS(ILSP[Half],$G13,ExceedKPI[KPI Structure],$A7,ILSP[Month],$B$6,ExceedKPI[Exceed KPI Hrs],">0"),"")
G7:G11	G7	=IF(E7<>F7,E7/F7,"")
E9:E11	E9	=SUMIFS(ExceedKPI[Exceed KPI Hrs],ExceedKPI[KPI Structure],$A9,ILSP[Month],$B$6)
F9:F11	F9	=IFERROR(COUNTIFS(ExceedKPI[KPI Structure],$A9,ILSP[Month],$B$6,ExceedKPI[Exceed KPI Hrs],">0"),"")
A15:A24	A15	=ILSP[@[Mus-No]]&ILSP[@Site]
B15:B24	B15	=IFERROR(EOMONTH([@[Date & Time ]],-1)+1,"")
E15:E24	E15	=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
F15:F24	F15	=IF(VLOOKUP([@[Mus-No]],'ILSP Vessel Activity Tracking '!$AK$15:$BN$3810,1,0)=[@[Mus-No]],VLOOKUP([@[Mus-No]],'ILSP Vessel Activity Tracking '!$AK$15:$BN$3810,12,0),NA())
G15:G24	G15	=IF(VLOOKUP([@[Mus-No]],'ILSP Vessel Activity Tracking '!$AK$15:$BN$3810,1,0)=[@[Mus-No]],VLOOKUP([@[Mus-No]],'ILSP Vessel Activity Tracking '!$AK$15:$BN$3810,4,0),NA())
M15:M24	M15	=IFERROR(IF(VLOOKUP([@[ Location Site''s]],LocationSite,1,0)=[@[ Location Site''s]],VLOOKUP([@[ Location Site''s]],LocationSite,2,0),NA()),"")
N15	N15	=IF(COUNTIFS(H15:$H$15,H15,C15:$C$15,C15)=1,H15,"")
O15:O24	O15	=IF([@[ Location Site''s]]="","",SUMIFS(ILSP[Spent Hrs],ILSP[Site],ILSP[@Site],ILSP[Mus-No],ILSP[@[Mus-No]]))
P15:P24	P15	=IF(AND(G15=$G$13,M15=$A$7,O15>$C$13),O15-$C$13,IF(AND(G15=$G$14,M15=$A$8,O15>$D$13),O15-$D$13,IF(AND(M15=$A$9,O15>$F$13),O15-$F$13,IF(AND(M15=$A$10,O15>$D$13),O15-$F$13,IF(AND(M15=$A$11,O15>$E$13),O15-$E$13,"")))))
N16:N24	N16	=IF(COUNTIFS(H$15:$H16,H16,C$15:$C16,C16)=1,H16,"")
K15:K24	K15	=IFERROR(1/(1/SUM([@[Date & Time]]-[@[Date & Time ]])),"")

Cells with Data Validation
Cell	Allow	Criteria
B6	List	=UniqueList!$R$3:$R$30

 

[KRice]

Have you tried manually computing the Exceed KPI (hours) values in my example? Are they incorrect?

As I mentioned before, when you post a mini-sheet with references to other workbooks/worksheets, those values appear as errors...so I can't use your latest post without retyping and manually entering values...which I am not going to do.

 

[Lukma]

Dear Krice

Thanks for all effort i appreciate

 

[KRice]

You're welcome. Please post back with details if you discover any issues. As mentioned, "Prd-Barge" appears in column O and there is no corresponding match for it in A4:A8, so that may be a problem. Also the criteria for performing the counting and average hours in the summary table follows your lead and constrains the items being considered to only those falling within the month-year specified and having a non-zero "Exceed KPI" value (items with 0 are ignored)...I don't know if this is desired. It may be that you want 0's to appear in the Exceed KPI column, like this:

MrExcel_20220715b.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		ILSP	Deck Load Threshold	KPI (h)	No's of Visit	Total Hours	Average Hours
4	Rigs&Barges	JackRigs	>=70%	20	1	43	43
5	Rigs&Barges	JackRigs	<=69%	14	0	0	N/A
6	Complex	Complex	N/A	12	1	14.7666667	14.7666667
7	Drl-Island	Drl-Island	N/A	10	0	0	N/A
8	Prd-Island	Prd-Island	N/A	10	0	0	N/A
9		April-22
10
11		Month	Voyage	Vessel	Location	Deck Load %	Location Start Date & Time	Location End Date & Time	Total Time		Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent (d)	Exceed KPI (h)
12	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.2986	44678.3347	0.0361111		Apr-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
13	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.4028	44678.4083	0.0055556		May-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
14	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.2986	44679.3278	0.0291667		May-22	1044	Mutawa-202	0%		0.01041667	0
15	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.3438	44679.3493	0.0055556		Apr-22	1045	Rig Vivekanand-3	100%	Rigs&Barges	2.625	43
16	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.2847	44682.2965	0.0118056		Apr-22	1045	Rig Yemillah	0%	Rigs&Barges	0.50694444	0
17	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.3507	44683.3563	1.0055556		Apr-22	1046	Nasr Complex	0%		0.42708333	0
18	1044 Mutawa-202	May-22	1044	Z-QUEEN	Mutawa-202	0%	44682.6042	44682.6146	0.0104167		Apr-22	1046	Barge Pepper	20%	Prd-Barge	0.10416667	0
19	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.3715	44683.3847	0.0131944		Apr-22	1047	Al Qatia Island	50%	Drl-Island	0.02777778	0
20	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.4444	44683.4528	0.0083333		Jan-00	0	0	0%		0	0
21	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44678.6458	44678.8403	0.1944444
22	1045 Rig Yemillah	Apr-22	1045	QMS MARIMBA	Rig Yemillah	0%	44678.9306	44679.4375	0.5069444
23	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44679.5	44681.9306	2.4305556
24	1046 Nasr Complex	Apr-22	1046	ADNOC-810	Nasr Complex	0%	44678.3333	44678.7604	0.4270833
25	1046 Barge Pepper	Apr-22	1046	ADNOC-810	Barge Pepper	20%	44678.8958	44679	0.1041667
26	1047 Al Qatia Island	Apr-22	1047	LCT-WARDEH	Al Qatia Island	50%	44679.0833	44679.1111	0.0277778
Sheet9

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$Q$12:$Q$900000,">0",$N$12:$N$900000,">=70%")
F4	F4	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$N$12:$N$900000,">=70%")
G4:G8	G4	=IFERROR(F4/E4,"N/A")
E5	E5	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$Q$12:$Q$900000,">0",$N$12:$N$900000,"<=69%")
F5	F5	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$N$12:$N$900000,"<=69%")
E6:E8	E6	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A6,$Q$12:$Q$900000,">0")
F6:F8	F6	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A6)
A12:A26	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N20	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
P12:P20	P12	=SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)
Q12:Q20	Q12	=IF(AND(O12="Rigs&Barges",N12>0.69,N12<0.7),"undefined",MAX(0,P12*24-IF(O12="Rigs&Barges",(N12>=0.7)*$D$4 + (N12<=0.69)*$D$5, IFERROR(INDEX($D$6:$D$8,MATCH(O12,$A$6:$A$8,0)),P12*24))))
O14,O20:O26,O17	O14	=IFERROR(VLOOKUP(M14,Location,2,0),"")
I12:I26	I12	=[@[Location End Date & Time ]]-[@[Location Start Date & Time ]]
Dynamic array formulas.