Hi Friends
Please can anyone help out with my data for vessel utilization Report, i need to Calculate the percentage for Each criteria
1. In Column B69 i have total numbers of (Time in Days ) for the Productive time
2. In Column B86 i have total numbers of (Time in Days ) for the Non Productive Time
3. In Column B89 i have total numbers of (Time in Days ) for Column B87 Maintenance & B88 Port Crew Change
4. In Column B90 i have total numbers of (Time in Days ) Vessels off-Hire
Now i need to Know the Total numbers of days Vessels worked in Column B91 and In Column B93 to get the percentage of vessel Availability and in Column B95 to get the Productive Time
I will be glad if some can work out on my data with a solution
Regards
Please can anyone help out with my data for vessel utilization Report, i need to Calculate the percentage for Each criteria
1. In Column B69 i have total numbers of (Time in Days ) for the Productive time
2. In Column B86 i have total numbers of (Time in Days ) for the Non Productive Time
3. In Column B89 i have total numbers of (Time in Days ) for Column B87 Maintenance & B88 Port Crew Change
4. In Column B90 i have total numbers of (Time in Days ) Vessels off-Hire
Now i need to Know the Total numbers of days Vessels worked in Column B91 and In Column B93 to get the percentage of vessel Availability and in Column B95 to get the Productive Time
I will be glad if some can work out on my data with a solution
Regards
| Supply Vessels Utilization Reports 2021 Review.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 59 | Supply Vessels Productive Time | ADNOC-222 | ADNOC-223 | ADNOC-225 | ADNOC-226 | ADNOC-229 | A-CHLOE | ADNOC-511 | CECILIE-K | M-SUPPORTER | Z-POWER | A-LIBERTY | ADNOC-950 | B-HOMERE | Total Hours | Total Days | Percentage % | ||
| 60 | Loading/ Offload ( Adnoc L&S Base ) | 4.3 | 2.5 | 0.9 | 0.1 | 3.9 | 1.6 | 1.2 | 3.3 | 3.8 | 3.5 | 2.3 | 2.3 | 3.7 | 802:12 | 33.43 | 11% | ||
| 61 | Loading/ Offload (Free-Port) | ||||||||||||||||||
| 62 | Loading/ Offload Drilling / Artificial Islands | 1.6 | 0.3 | 0.3 | 0.1 | 0.0 | 3.0 | 3.4 | 207:12 | 8.63 | 3% | ||||||||
| 63 | Loading/ Offload Jackup Rigs | 11.7 | 2.9 | 24.8 | 13.9 | 7.3 | 1.3 | 6.5 | 8.6 | 9.3 | 8.1 | 5.8 | 11.0 | 2668:48 | 111.20 | 38% | |||
| 64 | Loading/ Offload Barges | 0.1 | 28.3 | 1.9 | 0.5 | 736:54 | 30.70 | 10% | |||||||||||
| 65 | Loading/ Offload ( Complexes ) | ||||||||||||||||||
| 66 | Loading/ Offload Prd-Island (DAS / Arzanah/ ZIRKU) | 0.1 | 0.2 | 0.6 | 0.4 | 32:48 | 1.37 | 0% | |||||||||||
| 67 | Others / Marine/Rig Move | 0.0 | 0.1 | 18.5 | 1.7 | 0.6 | 501:48 | 20.91 | 7% | ||||||||||
| 68 | In Transit / Steaming to Location | 8.9 | 3.9 | 2.8 | 1.6 | 7.2 | 7.4 | 5.9 | 9.6 | 7.7 | 7.3 | 9.5 | 11.6 | 5.3 | 2122:54 | 88.45 | 30% | ||
| 69 | Sub Total | 25.1 | 11.0 | 28.8 | 30.2 | 25.1 | 16.2 | 27.1 | 21.3 | 20.0 | 22.8 | 23.0 | 23.5 | 20.6 | 7072:36 | 294.7 | 76% | ||
| 70 | Supply Vessel's Non-Productive Time | ||||||||||||||||||
| 71 | Waiting Drilling Supply Material | 0.2 | 1.0 | 0.9 | 0.5 | 0.3 | 1.3 | 0.5 | 112:30 | 4.69 | 8% | ||||||||
| 72 | Waiting For Adnoc L&S Base Operations Readines | 0.1 | 1.8 | 0.3 | 1.5 | 0.5 | 2.4 | 2.2 | 1.0 | 230:12 | 9.59 | 15% | |||||||
| 73 | Waiting For Free-Port/Mus-Port Readines | 0.5 | 12:00 | 0.50 | 1% | ||||||||||||||
| 74 | Waiting On VTS Permission | 0.1 | 0.6 | 0.1 | 0.2 | 0.2 | 0.1 | 0.3 | 0.0 | 0.3 | 45:30 | 1.90 | 3% | ||||||
| 75 | Waiting On Channel Closure | 0.3 | 0.1 | 0.4 | 0.0 | 19:06 | 0.80 | 1% | |||||||||||
| 76 | Waiting Berthing Adnoc L&S | 1.7 | 0.4 | 0.2 | 0.0 | 0.2 | 0.1 | 64:12 | 2.68 | 4% | |||||||||
| 77 | Waiting On DayLight Complexes | ||||||||||||||||||
| 78 | Waiting On Prd-Island (DAS / Arzanah/ ZIRKU) | 1.4 | 0.3 | 42:00 | 1.75 | 3% | |||||||||||||
| 79 | Waiting Instruction Rigs/Island | 0.1 | 1:18 | 0.05 | 0% | ||||||||||||||
| 80 | Waiting On Jackup Rigs | 1.4 | 3.8 | 1.0 | 0.4 | 158:00 | 6.58 | 11% | |||||||||||
| 81 | Waiting on Barges & Field Vessels | ||||||||||||||||||
| 82 | Waiting On DayLight / Artificial Islands/ Prd-Island | 0.6 | 0.5 | 0.2 | 1.8 | 0.5 | 86:36 | 3.61 | 6% | ||||||||||
| 83 | Waiting Instruction Artifical Island | 1.0 | 0.1 | 1.1 | 53:36 | 2.23 | 4% | ||||||||||||
| 84 | Waiting On Weather ( Wind Speed / Sea Condition ) | 4.8 | 0.3 | 0.1 | 5.3 | 2.6 | 4.4 | 4.8 | 2.4 | 2.9 | 662:54 | 27.62 | 45% | ||||||
| 85 | Waiting on Weather (Fog / poor visibility) | ||||||||||||||||||
| 86 | Sub Total | 4.8 | 1.0 | 1.3 | 4.9 | 8.9 | 2.9 | 8.7 | 6.2 | 7.2 | 6.9 | 6.6 | 2.7 | 1487:54 | 62.0 | 16% | |||
| 87 | Maintenance | 4.9 | 3.8 | 209:00 | 8.7 | 100% | |||||||||||||
| 88 | Port Crew Change / Supply | ||||||||||||||||||
| 89 | Sub- TOTAL | 4.9 | 3.8 | 209:00 | 9 | 2% | |||||||||||||
| 90 | Off-Hire | 18.0 | 6.7 | 432:00 | 18.0 | 5% | |||||||||||||
| 91 | TOTAL Days | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 9362:30 | 390.1 | 98% | ||
| 92 | |||||||||||||||||||
| 93 | Vessel Availability | 100% | 40% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 78% | 94% | 94% | 98% | ||
| 94 | On Mnt's | On Mnt's | |||||||||||||||||
| 95 | Productive Time | 84% | 37% | 96% | 100% | 84% | 54% | 90% | 71% | 67% | 76% | 77% | 78% | 69% | 76% | 76% | 77% | ||
Supply_Vessels_Utilization | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C90:O90,C87:O88,C71:O85,C60:O68 | C60 | =IFERROR(1/(1/SUMIFS(INDEX($BC$8:$BZ$1519,0,MATCH($B$6,$BC$7:$BZ$7,0)),$BA$8:$BA$1519,C$59,$BB$8:$BB$1519,$B60)),"") |
| P87:P88,P71:P85,P60:P68 | P60 | =IFERROR(1/(1/SUM(C60:O60)),"") |
| Q87:Q88,Q71:Q85,Q60:Q68 | Q60 | =IFERROR(1/(1/SUM(C60:O60)),"") |
| R60:R68 | R60 | =IFERROR(SUM(Q60/$Q$69),"") |
| C69:O69 | C69 | =IFERROR(1/(1/SUM(C60:C68)),"") |
| P69:Q69 | P69 | =SUM(P60:P68) |
| R69 | R69 | =IFERROR(SUM(Q69/Q91),"") |
| R71:R85 | R71 | =IFERROR(SUM(Q71/$Q$86),"") |
| C86:O86 | C86 | =IFERROR(1/(1/SUM(C71:C85)),"") |
| P86:Q86 | P86 | =SUM(P71:P85) |
| R86 | R86 | =IFERROR(SUM(Q86/Q91),"") |
| R87:R89 | R87 | =IFERROR(SUM(Q87/Q89),"") |
| C89:O89 | C89 | =IFERROR(1/(1/SUM(C87:C88)),"") |
| P89:Q89 | P89 | =SUM(P87:P88) |
| P90 | P90 | =IFERROR(1/(1/SUM(C90:M90)),"") |
| Q90 | Q90 | =IFERROR(1/(1/SUM(C90:M90)),"") |
| R90 | R90 | =IFERROR(SUM(Q90/Q91),"") |
| C91:O91 | C91 | =IFERROR(1/(1/SUM(C69,C86,C89:C90)),"") |
| P91 | P91 | =SUM(C91:O91) |
| Q91 | Q91 | =SUM(C91:O91) |
| R91 | R91 | =IFERROR(SUM(R69,R86,R89,R90),"") |
| C93:Q93 | C93 | =IFERROR(SUM(C69,C86,C89)/C91,"") |
| R93 | R93 | =IFERROR(SUM(R69,R86,R89,R90),"") |
| C94:O94 | C94 | =IF(C87="",C87,IF(C87>=0.1,"On Mnt's",IF(C87="",""))) |
| C95:O95 | C95 | =IFERROR(SUM(C69)/C91,"") |
| P95:R95 | P95 | =SUM(P69/P91) |
