Help With Correction of Formula in my Data.

L

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
3ILSPKPINo's of VisitTotal HoursAverage hrs.
4Rigs&BargesJackRigs70%16269:3516:50
5Rigs&BargesJackRigs69%44302:306:52
6ComplexComplex12Hrs22143:316:31
7Drl-IslandDrl-Island10 Hrs33175:505:19
8Prd-IslandPrd-Island10 Hrs57:351:31
9June70%69%
10ILSP KPI20141210
11MonthVoyageVessel LocationDeck Load %Location Start Date & Time Location End Date & Time Total TimeMonthVoyageLocationDeck %Jackup ComplexActual Time SpentExceed KPI
121044 ABK ComplexApril1044Z-QUEENABK Complex80%27/04/22 07:1027/04/22 08:020:52April1044ABK Complex80%Complex26:4614:46
131044 ABK ComplexApril1044Z-QUEENABK Complex80%27/04/22 09:4027/04/22 09:480:08May1044ABK Complex80%Complex26:4614:46
141044 ABK ComplexApril1044Z-QUEENABK Complex80%28/04/22 07:1028/04/22 07:520:42May1044Mutawa-2020% 0:15 
151044 ABK ComplexApril1044Z-QUEENABK Complex80%28/04/22 08:1528/04/22 08:230:08April1045Rig Vivekanand-3100%Rigs&Barges63:0043:00
161044 ABK ComplexMay1044Z-QUEENABK Complex80%01/05/22 06:5001/05/22 07:070:17April1045Rig Yemillah0%Rigs&Barges12:10 
171044 ABK ComplexMay1044Z-QUEENABK Complex80%01/05/22 08:2502/05/22 08:3324:08April1046Nasr Complex 0% 10:15 
181044 Mutawa-202May1044Z-QUEENMutawa-2020%01/05/22 14:3001/05/22 14:450:15April1046Barge Pepper20%Prd-Barge2:30 
191044 ABK ComplexMay1044Z-QUEENABK Complex80%02/05/22 08:5502/05/22 09:140:19April1047Al Qatia Island50%Drl-Island2:34 
201044 ABK ComplexMay1044Z-QUEENABK Complex80%02/05/22 10:4002/05/22 10:520:12April1047Bu Sikeen Island20%Drl-Island0:33 
211045 Rig Vivekanand-3April1045QMS MARIMBARig Vivekanand-3100%27/04/22 15:3027/04/22 20:104:40April1048Al Ghallan Island10%Drl-Island0:20 
221045 Rig YemillahApril1045QMS MARIMBARig Yemillah0%27/04/22 22:2028/04/22 10:3012:10April1048Umm Al Anbar30%Drl-Island10:500:50
231045 Rig Vivekanand-3April1045QMS MARIMBARig Vivekanand-3100%28/04/22 12:0030/04/22 22:2058:20April1048Asseifiya Island10%Drl-Island5:30 
241046 Nasr Complex April1046ADNOC-810Nasr Complex 0%27/04/22 08:0027/04/22 18:1510:15April1048Ettouk Island60%Drl-Island8:35 
251046 Barge PepperApril1046ADNOC-810Barge Pepper20%27/04/22 21:3028/04/22 00:002:30April1049Rig Junana0%Rigs&Barges0:05 
261047 Al Qatia IslandApril1047LCT-WARDEHAl Qatia Island50%28/04/22 02:0028/04/22 02:400:40April1049Rig SMS Esse0%Rigs&Barges19:15 
ILSP_Offshore_Vessel_Tracking
Cell Formulas
RangeFormula
D4D4=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9,$N$12:$N$900000,">=70%")
E4E4=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,">=70%")
F4:F8F4=E4/D4
D5D5=COUNTIFS($Q$12:$Q$900000,">=0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,"<=69%")
E5E5=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,"<=69%")
D6D6=COUNTIFS($O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E6E6=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9)
D7D7=COUNTIFS($O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E7E7=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9)
D8D8=COUNTIFS($O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E8E8=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9)
A12:A26A12=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26B12=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N1739K12=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
O12:O26O12=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P26P12=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q26Q12=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:F26F12=IFERROR(VLOOKUP(A12,Info_Setting!$P$4:$S$600000,4,0),"0%")
I12:I26I12=IFERROR(1/(1/SUM([@[Location End Date & Time ]]-[@[Location Start Date & Time ]])),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B9List=Info_Setting!$K$3:$K$15
 
Sort by date Sort by votes
KRice said:
I see that you posted a near duplicate request here:
www.mrexcel.com

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 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
ABCDEFGHIJKLMNOPQ
1
2
3ILSPDeck Load ThresholdKPI (h)No's of VisitTotal HoursAverage Hours
4Rigs&BargesJackRigs>=70%2014343
5Rigs&BargesJackRigs<=69%1400N/A
6ComplexComplexN/A12114.766666714.7666667
7Drl-IslandDrl-IslandN/A1000N/A
8Prd-IslandPrd-IslandN/A1000N/A
9April-22
10
11MonthVoyageVessel LocationDeck Load %Location Start Date & Time Location End Date & Time Total TimeMonthVoyageLocationDeck %Jackup ComplexActual Time Spent (d)Exceed KPI (h)
121044 ABK ComplexApr-221044Z-QUEENABK Complex80%44678.298644678.33470.0361111Apr-221044ABK Complex80%Complex1.1152777814.766667
131044 ABK ComplexApr-221044Z-QUEENABK Complex80%44678.402844678.40830.0055556May-221044ABK Complex80%Complex1.1152777814.766667
141044 ABK ComplexApr-221044Z-QUEENABK Complex80%44679.298644679.32780.0291667May-221044Mutawa-2020% 0.010416670.25
151044 ABK ComplexApr-221044Z-QUEENABK Complex80%44679.343844679.34930.0055556Apr-221045Rig Vivekanand-3100%Rigs&Barges2.62543
161044 ABK ComplexMay-221044Z-QUEENABK Complex80%44682.284744682.29650.0118056Apr-221045Rig Yemillah0%Rigs&Barges0.506944440
171044 ABK ComplexMay-221044Z-QUEENABK Complex80%44682.350744683.35631.0055556Apr-221046Nasr Complex 0% 0.4270833310.25
181044 Mutawa-202May-221044Z-QUEENMutawa-2020%44682.604244682.61460.0104167Apr-221046Barge Pepper20%Prd-Barge0.104166672.5
191044 ABK ComplexMay-221044Z-QUEENABK Complex80%44683.371544683.38470.0131944Apr-221047Al Qatia Island50%Drl-Island0.027777780
201044 ABK ComplexMay-221044Z-QUEENABK Complex80%44683.444444683.45280.0083333Jan-00000% 00
211045 Rig Vivekanand-3Apr-221045QMS MARIMBARig Vivekanand-3100%44678.645844678.84030.1944444 
221045 Rig YemillahApr-221045QMS MARIMBARig Yemillah0%44678.930644679.43750.5069444 
231045 Rig Vivekanand-3Apr-221045QMS MARIMBARig Vivekanand-3100%44679.544681.93062.4305556 
241046 Nasr Complex Apr-221046ADNOC-810Nasr Complex 0%44678.333344678.76040.4270833 
251046 Barge PepperApr-221046ADNOC-810Barge Pepper20%44678.8958446790.1041667 
261047 Al Qatia IslandApr-221047LCT-WARDEHAl Qatia Island50%44679.083344679.11110.0277778 
Sheet9
Cell Formulas
RangeFormula
E4E4=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$Q$12:$Q$900000,">0",$N$12:$N$900000,">=70%")
F4F4=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$N$12:$N$900000,">=70%")
G4:G8G4=IFERROR(F4/E4,"N/A")
E5E5=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$Q$12:$Q$900000,">0",$N$12:$N$900000,"<=69%")
F5F5=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$N$12:$N$900000,"<=69%")
E6:E8E6=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A6,$Q$12:$Q$900000,">0")
F6:F8F6=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A6)
A12:A26A12=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26B12=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N20K12=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
P12:P20P12=SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)
Q12:Q20Q12=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,O17O14=IFERROR(VLOOKUP(M14,Location,2,0),"")
I12:I26I12=[@[Location End Date & Time ]]-[@[Location Start Date & Time ]]
Dynamic array formulas.
Click to expand...
Krice

Thank you, however like i said this is the KPI

[
1661840764462.png


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
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Lukma said:
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
Click to expand...
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOP
6JuneILSP Performance Leads Total Hrs No's of VisitAvrg Hrs.
7Rigs&BargesAvrg Time Full Deck Jackup Rigs <20152:231015:14
8Rigs&BargesAvrg Time Half Deck Jackup Rigs <14641:50709:10
9Drl-IslandAvrg Time Drilling Island <10175:5315:38
10Prd-IslandAvrg Time Dlight Prd-Island Rigs <104:3514:35
11Prd-ComplexAvrg Time Daylight Complex <12143:56236:15
12KPI Control
13PILSP KPI20141210Full
14DMonthMus-NoSupply VesselVSL D/PDep ADL&S Date & Time HalfSiteDate & Time Date & TimeSpent HrsKPI Structure Location Site'sTotal Hrs SpentExceed KPI Hrs
151Rig MarawwahJanuary1SEACOR PARANAD02/01/22 11:00HalfRig Marawwah03/01/22 23:1504/01/22 03:504:35Rigs&BargesRig Marawwah0.21 
161Rig MarawwahJanuary1SEACOR PARANAD02/01/22 11:00HalfRig Marawwah04/01/22 04:5504/01/22 05:280:33    
172ACPTJanuary2ADNOC-850P03/01/22 03:00Full ACPT03/01/22 13:0003/01/22 19:206:20Prd-ComplexACPT0.26 
182ADNOC-710January2ADNOC-850P03/01/22 03:00Full ADNOC-71003/01/22 21:1503/01/22 22:151:00 ADNOC-7100.04 
193Rig QarninJanuary3ADNOC-S01D01/01/22 18:10HalfRig Qarnin03/01/22 07:3603/01/22 20:0012:24Rigs&BargesRig Qarnin0.52 
204Rig JunanaJanuary4M-SUPPORTERD02/01/22 13:10Full Rig Junana03/01/22 02:0003/01/22 03:251:25Rigs&BargesRig Junana0.50 
214Rig JunanaJanuary4M-SUPPORTERD02/01/22 13:10Full Rig Junana03/01/22 06:5003/01/22 17:3010:40    
224Barge PrideJanuary4M-SUPPORTERD02/01/22 13:10Full Barge Pride03/01/22 19:1003/01/22 22:153:05Rigs&BargesBarge Pride0.13 
234Rig Ariabahatt-1January4M-SUPPORTERD02/01/22 13:10Full Rig Ariabahatt-104/01/22 14:0004/01/22 22:008:00Rigs&BargesRig Ariabahatt-10.33 
245Al Ghallan IslandJanuary5ADNOC-221D02/01/22 10:40Full Al Ghallan Island02/01/22 22:3503/01/22 20:0021:25Drl-IslandAl Ghallan Island0.890.48
ILSP Vessel Activity Tracking
Cell Formulas
RangeFormula
E7:E8E7=SUMIFS(ExceedKPI[Exceed KPI Hrs],ExceedKPI[KPI Structure],$A7,ILSP[Half],$G13,ILSP[Month],$B$6)
F7:F8F7=IFERROR(COUNTIFS(ILSP[Half],$G13,ExceedKPI[KPI Structure],$A7,ILSP[Month],$B$6,ExceedKPI[Exceed KPI Hrs],">0"),"")
G7:G11G7=IF(E7<>F7,E7/F7,"")
E9:E11E9=SUMIFS(ExceedKPI[Exceed KPI Hrs],ExceedKPI[KPI Structure],$A9,ILSP[Month],$B$6)
F9:F11F9=IFERROR(COUNTIFS(ExceedKPI[KPI Structure],$A9,ILSP[Month],$B$6,ExceedKPI[Exceed KPI Hrs],">0"),"")
A15:A24A15=ILSP[@[Mus-No]]&ILSP[@Site]
B15:B24B15=IFERROR(EOMONTH([@[Date & Time ]],-1)+1,"")
E15:E24E15=IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"")
F15:F24F15=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:G24G15=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:M24M15=IFERROR(IF(VLOOKUP([@[ Location Site''s]],LocationSite,1,0)=[@[ Location Site''s]],VLOOKUP([@[ Location Site''s]],LocationSite,2,0),NA()),"")
N15N15=IF(COUNTIFS(H15:$H$15,H15,C15:$C$15,C15)=1,H15,"")
O15:O24O15=IF([@[ Location Site''s]]="","",SUMIFS(ILSP[Spent Hrs],ILSP[Site],ILSP[@Site],ILSP[Mus-No],ILSP[@[Mus-No]]))
P15:P24P15=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:N24N16=IF(COUNTIFS(H$15:$H16,H16,C$15:$C16,C16)=1,H16,"")
K15:K24K15=IFERROR(1/(1/SUM([@[Date & Time]]-[@[Date & Time ]])),"")
Cells with Data Validation
CellAllowCriteria
B6List=UniqueList!$R$3:$R$30
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
ABCDEFGHIJKLMNOPQ
3ILSPDeck Load ThresholdKPI (h)No's of VisitTotal HoursAverage Hours
4Rigs&BargesJackRigs>=70%2014343
5Rigs&BargesJackRigs<=69%1400N/A
6ComplexComplexN/A12114.766666714.7666667
7Drl-IslandDrl-IslandN/A1000N/A
8Prd-IslandPrd-IslandN/A1000N/A
9April-22
10
11MonthVoyageVessel LocationDeck Load %Location Start Date & Time Location End Date & Time Total TimeMonthVoyageLocationDeck %Jackup ComplexActual Time Spent (d)Exceed KPI (h)
121044 ABK ComplexApr-221044Z-QUEENABK Complex80%44678.298644678.33470.0361111Apr-221044ABK Complex80%Complex1.1152777814.766667
131044 ABK ComplexApr-221044Z-QUEENABK Complex80%44678.402844678.40830.0055556May-221044ABK Complex80%Complex1.1152777814.766667
141044 ABK ComplexApr-221044Z-QUEENABK Complex80%44679.298644679.32780.0291667May-221044Mutawa-2020% 0.010416670
151044 ABK ComplexApr-221044Z-QUEENABK Complex80%44679.343844679.34930.0055556Apr-221045Rig Vivekanand-3100%Rigs&Barges2.62543
161044 ABK ComplexMay-221044Z-QUEENABK Complex80%44682.284744682.29650.0118056Apr-221045Rig Yemillah0%Rigs&Barges0.506944440
171044 ABK ComplexMay-221044Z-QUEENABK Complex80%44682.350744683.35631.0055556Apr-221046Nasr Complex 0% 0.427083330
181044 Mutawa-202May-221044Z-QUEENMutawa-2020%44682.604244682.61460.0104167Apr-221046Barge Pepper20%Prd-Barge0.104166670
191044 ABK ComplexMay-221044Z-QUEENABK Complex80%44683.371544683.38470.0131944Apr-221047Al Qatia Island50%Drl-Island0.027777780
201044 ABK ComplexMay-221044Z-QUEENABK Complex80%44683.444444683.45280.0083333Jan-00000% 00
211045 Rig Vivekanand-3Apr-221045QMS MARIMBARig Vivekanand-3100%44678.645844678.84030.1944444 
221045 Rig YemillahApr-221045QMS MARIMBARig Yemillah0%44678.930644679.43750.5069444 
231045 Rig Vivekanand-3Apr-221045QMS MARIMBARig Vivekanand-3100%44679.544681.93062.4305556 
241046 Nasr Complex Apr-221046ADNOC-810Nasr Complex 0%44678.333344678.76040.4270833 
251046 Barge PepperApr-221046ADNOC-810Barge Pepper20%44678.8958446790.1041667 
261047 Al Qatia IslandApr-221047LCT-WARDEHAl Qatia Island50%44679.083344679.11110.0277778 
Sheet9
Cell Formulas
RangeFormula
E4E4=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$Q$12:$Q$900000,">0",$N$12:$N$900000,">=70%")
F4F4=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$N$12:$N$900000,">=70%")
G4:G8G4=IFERROR(F4/E4,"N/A")
E5E5=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$Q$12:$Q$900000,">0",$N$12:$N$900000,"<=69%")
F5F5=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$N$12:$N$900000,"<=69%")
E6:E8E6=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A6,$Q$12:$Q$900000,">0")
F6:F8F6=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A6)
A12:A26A12=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26B12=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N20K12=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
P12:P20P12=SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)
Q12:Q20Q12=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,O17O14=IFERROR(VLOOKUP(M14,Location,2,0),"")
I12:I26I12=[@[Location End Date & Time ]]-[@[Location Start Date & Time ]]
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Problem with XLOOKUP on array
Replies
3
Views
201
Fluff
Fluff
S
  • Solved
Convert to dynamic array
Replies
7
Views
152
sharshra
S
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
132
FormulasOnly
F
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
60
aurelius33
A

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top