Need Help With a Formula reposting my Data

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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 <=20hrs on rigs/barges
2. <=69 Deck Load on any Location show spend <=14hrs on rigs/barges
3 Complex is 12 hrs.
4 prd-Drilling 10hrs
5. drl-Island 10hrs

In Column M12 i have use a unique to extract the list of Location accordingly
In Column N12 i have use a unique to extract Percentage for the Location accordingly
In Column P12 i have use sumifs to sum the actual Hours for Each Voyage Spent

Now In Column Q12 i need a Formula that Will Subtract the KPI from the Actual Hours base of Criteria of The KPI percentage
AND IF >=70% to 100% should subtract 20hrs from the actual in Column P Jackup-Rig/Barges
AND IF <=69 % From 0% to 69% should subtract 14hrs from the actual in Column P for Jackup-Rig/Barges
AND IF Complex should subtract 12hrs from the actual in Column P for Complex
AND IF Prd-Island should subtract 10hrs from the actual in Column P for Complex
AND IF drl-Island should subtract 10hrs from the actual in Column P for Complex


Then in Column D4 i need to count numbers of Exceed in Column Q based on Criteria Range of Rigs/Barges and >=70% from 70% to 100%
Then in Column D5 i need to count numbers of Exceed in Column Q based on Criteria Range of Rigs/Barges and <=69% From 0% to 69%
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Complex
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Prd-Island
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Drl-Island

Then Column E4 i need to sum all hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and >=70%
Then Column E4 i need to sum all hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and <=69%
Then Column F4 i need get the average hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and >=70%
Then Column F4 i need get the average that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and <=69%

1660466126121.png

I hope am Able to explain better with the formula i required as a help from friends also i have Highlight an Example

Regards

ILSP Offshore Vessel Tracking Time Spent.xlsx
ABCDEFGHIJKLMNOPQ
3ILSPKPINo's of VisitTotal HoursAverage hrs.
4Rigs&BargesJackRigs>=70%
5Rigs&BargesJackRigs<=69%
6ComplexComplex12Hrs
7Drl-IslandDrl-Island10 Hrs
8Prd-IslandPrd-Island10 Hrs
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:252: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
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)),"")
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
For reference, here is the earlier post related to this one...
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,150
Members
453,021
Latest member
Justyna P

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