Help With Correction of Formula in my Data.

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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I have a few questions right now:
  1. How long is your worksheet...or rather, as time goes on, how many rows would you expect it not to exceed?
  2. In the O12 formula (and down), the name "Location" is mentioned. What is that? It appears to be at least a two-column array, but I don't see it defined anywhere. Consequently, my sheet shows all blanks in this column.
  3. In the F12 formula (and down), a worksheet named Info_Setting is referenced. Are all of the values in column P (P4 and down) in ascending order, no exceptions? Right now, my sheet shows 0% in this column because the reference is not available. I can hardwire the values from your mini-table, but wanted to confirm that VLOOKUP should not produce incorrect results due to any potential sorting issue...this is why I rarely use VLOOKUP.
  4. In O18, I see Prd-Barge. I don't see any matching items in A4:A8. Should I?
 
Last edited:
Upvote 0
In Q12 (and down), the part highlighted in red leads to a condition that can never be TRUE unless $A$4 and O12 are blank. What is this part of the formula supposed to be doing? Is the 1st O12="" supposed to be N12=""?
=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,""))))))
 
Upvote 0
How would a Deck % of 69.5% be categorized? It doesn't meet either of the Rigs&Barges criteria as currently written.
 
Upvote 0
Hi Krice

Thank you for for you feedback
well the KPI is set for 70%-100% deck load working hours 20hrs
and KPI for 0%-69% deck load working hours is 14 Hrs.

In Column Q i use this formula once the Actual hours in column P exceed any of the KPI then it should subtract hours to get the exceeding hours left in Q
now my trouble is the countifs to count the column Q based the KPI criteria in column D

Regards
 
Upvote 0
In my posts 2-4 I've asked 7 questions. Some of those questions and comments point out where I think some problems exist. Other questions would help me understand how your worksheet is supposed to work. Please review those questions and answer them if possible. I do not understand your answer about the Deck %. Does your answer mean that you could never see a deck percentage between 69% and 70%?
 
Upvote 0
Krice

Sorry for the delay in reply and wasn't feeling to strong and also my PC wasn't fix at work but now am able to reply
thanks for your feedback and am reporting my data to clear more on the formula i required

Column C4 KPI is if >=70% to 100% should Spent 20hrs on Jack-up Rigs/ Barges
Column C5 KPI is if <= 69% to 0% should Spent 14hrs on Jack-up Rigs/ Barges
Column C6 KPI for Complex should Spent 12hrs on Complex
Column C7 KPI is should Spent 10hrs on Drl-Island
Column C8 KPI is should Spent 10hrs on Prd-Island
Column P12 is the actual hours spent on each location sites
Column Q12 i need the formula subtract if the any location that exceed the KPI criteria and give me the exceeded hours in Column Q12

Then From Column D4 to Count numbers of Exceed based on Criteria of >=70% upward from Column Q based on Criteria
And Column E4 to Sum the total hours based on Criteria of <=69% downward from Column Q based on Criteria

My problem now is the formula in Column Q,, D, E, and F

Appreciate if my Explanation is Cleared looking forward to a solution.

ILSP Offshore Vessel Tracking Time Spent.xlsx
ABCDEFGHIJKLMNOPQ
3ILSPKPINo's of VisitTotal HoursAverage hrs.
4Rigs&BargesJackRigs>=70%18:258:25
5Rigs&BargesJackRigs<=69%00:00#DIV/0!
6ComplexComplex12Hrs00:00#DIV/0!
7Drl-IslandDrl-Island10 Hrs00:00#DIV/0!
8Prd-IslandPrd-Island10 Hrs00:00#DIV/0!
9June>=70%<=69%
10Hours20141210
11MonthVoyageVessel LocationDeck Load %Location Start Date & Time Location End Date & Time Total TimeMonthVoyageLocationDeck %Jackup ComplexActual Time SpentExceed KPI
121310 ACPTJune1310ADNOC-850ACPT40%01/06/22 10:1001/06/22 12:001:50June1310ACPT40%Complex6:25 
131310 Al Ghallan IslandJune1310ADNOC-850Al Ghallan Island0%01/06/22 13:4001/06/22 14:200:40June1310Al Ghallan Island0%Drl-Island0:40 
141310 ACPTJune1310ADNOC-850ACPT40%01/06/22 15:3001/06/22 15:500:20June1311Rig Junana80%Rigs&Barges22:258:25
151310 ACPTJune1310ADNOC-850ACPT40%01/06/22 16:1501/06/22 20:304:15June1311Rig Marawwah10%Rigs&Barges2:10 
161311 Rig JunanaJune1311M-SUPPORTERRig Junana80%01/06/22 03:5001/06/22 22:0018:10June1311Rig Al Bzoom0%Rigs&Barges6:45 
171311 Rig MarawwahJune1311M-SUPPORTERRig Marawwah10%02/06/22 05:3002/06/22 07:402:10June1311Rig Al Yasat0%Rigs&Barges1:20 
181311 Rig Al BzoomJune1311M-SUPPORTERRig Al Bzoom0%02/06/22 18:0003/06/22 00:456:45June1312ZNSAT0%Complex0:50 
191311 Rig JunanaJune1311M-SUPPORTERRig Junana80%03/06/22 04:3003/06/22 08:454:15June1312ACPT60%Complex10:40 
201311 Rig Al YasatJune1311M-SUPPORTERRig Al Yasat0%03/06/22 09:4003/06/22 11:001:20June1312Barge Leen25%Prd-Barge2:30 
211312 ZNSATJune1312ADNOC-851ZNSAT0%02/06/22 08:3002/06/22 09:200:50June1313Asseifiya Island20%Drl-Island2:30 
221312 ACPTJune1312ADNOC-851ACPT60%02/06/22 11:0502/06/22 15:304:25June1313Al Ghallan Island10%Drl-Island3:15 
231312 ACPTJune1312ADNOC-851ACPT60%02/06/22 17:1002/06/22 19:502:40June1313Ettouk Island70%Drl-Island7:30 
241312 ACPTJune1312ADNOC-851ACPT60%02/06/22 21:2003/06/22 00:102:50June1314Al Qatia Island0%Drl-Island6:30 
251312 ACPTJune1312ADNOC-851ACPT60%03/06/22 11:4003/06/22 12:250:45June1314Das Island 0% 10:15 
261312 Barge LeenJune1312ADNOC-851Barge Leen25%03/06/22 14:0003/06/22 16:302:30June1315Asseifiya Island15%Drl-Island1:10 
271313 Asseifiya IslandJune1313ADNOC-1010Asseifiya Island20%02/06/22 05:1002/06/22 07:102:00June1315Al Ghallan Island15%Drl-Island6:22 
281313 Al Ghallan IslandJune1313ADNOC-1010Al Ghallan Island10%02/06/22 13:4502/06/22 17:003:15June1315Umm Al Anbar20%Drl-Island5:45 
291313 Asseifiya IslandJune1313ADNOC-1010Asseifiya Island20%03/06/22 01:0003/06/22 01:300:30June1315Ettouk Island50%Drl-Island6:27 
301313 Ettouk IslandJune1313ADNOC-1010Ettouk Island70%03/06/22 21:4504/06/22 05:157:30June1316Al Qatia Island30%Drl-Island0:24 
311314 Al Qatia IslandJune1314LCT-MARWAH-1Al Qatia Island0%02/06/22 10:4502/06/22 17:156:30June1316Bu Sikeen Island50%Drl-Island1:24 
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,C4)
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,C5)
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:A31A12=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B31B12=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N32K12=UNIQUE(CHOOSE({1,2,3,4},B12:B8736,C12:C8736,E12:E8736,F12:F8736))
O12:O31O12=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P31P12=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q31Q12=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:F31F12=IFERROR(VLOOKUP(A12,Info_Setting!$P$4:$S$600000,4,0),"0%")
I12:I31I12=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
 
Upvote 0
In Q12 (and down), the part highlighted in red leads to a condition that can never be TRUE unless $A$4 and O12 are blank. What is this part of the formula supposed to be doing? Is the 1st O12="" supposed to be N12=""?
=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,""))))))
good Day Krice

Thank you for reply the red for zero which i have use VLOOKUP to determine the site which do not have deck percentage as Zero %

But actually am not to sure about my formula that's why i request for a solution

i have also explain further in my second post

Appreciate your help with a solution

Regards
 
Upvote 0
I'm sorry, but I do not understand what you are trying to do. I have asked numerous questions that might provide some clarity to me, but you have not answered any of those questions. Please review my post #6 and see that it references previous posts where the questions are asked. Here are two of the more critical questions...let's start with answers to these:

In the O12 formula (and down), the name "Location" is mentioned. What is that? It appears to be at least a two-column array, but I don't see it defined anywhere. Consequently, my sheet shows all blanks in this column.

In O18, I see Prd-Barge. I don't see any matching items in A4:A8. Should I?
 
Upvote 0
I see that you posted a near duplicate request here:
...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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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