Need s solution to sum and minus same time

L

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi friends

Am having trouble with this equation in column C
am Trying to Sum Column C17 and C34 then Subtract Column C37&38 to get the numbers of Days is there a way to go about this with a formula

Appreciate any support

Supply Vessels Utilization Reports 2021.xlsx
BC
7Supply Vessels Productive TimeADNOC-1010
8Loading/ Offload ( Adnoc L&S Base )4.3
9Loading/ Offload (Free-Port) 
10Loading/ Offload Drilling / Artificial Islands5.2
11Loading/ Offload Jackup Rigs 
12Loading/ Offload Barges 
13Loading/ Offload ( Complexes ) 
14Loading/ Offload Prd-Island (DAS / Arzanah/ ZIRKU)0.1
15Others / Marine/Rig Move 
16In Transit / Steaming to Location 12.8
17Sub Total 22.4
18Supply Vessel's Non-Productive Time
19Waiting Drilling Supply Material 0.1
20Waiting For Adnoc L&S Base Operations Readines0.5
21Waiting For Free-Port/Mus-Port Readines 
22Waiting On VTS Permission 
23Waiting On Channel Closure 
24Waiting Berthing Adnoc L&S0.8
25Waiting On DayLight Complexes 
26Waiting On Prd-Island (DAS / Arzanah/ ZIRKU) 
27Waiting Instruction Rigs/Island 
28Waiting On Jackup Rigs  
29Waiting on Barges & Field Vessels 
30Waiting On DayLight / Artificial Islands/ Prd-Island1.3
31Waiting Instruction Artifical Island3.3
32Waiting On Weather ( Wind Speed / Sea Condition ) 1.6
33Waiting on Weather (Fog / poor visibility) 
34Sub Total 7.6
35Maintenance  
36Port Crew Change / Supply 
37Sub- TOTAL 
38Off-Hire 
39TOTAL Days30
40
41Vessel Availability100%
42 
43Productive Time75%
Supply_Vessels_Utilization
Cell Formulas
RangeFormula
C38,C35:C36,C19:C33,C8:C16C8=IFERROR(1/(1/SUMIFS(INDEX($BB$8:$BY$1547,0,MATCH($B$6,$BB$7:$BY$7,0)),$AZ$8:$AZ$1547,C$7,$BA$8:$BA$1547,$B8)),"")
C17C17=IFERROR(1/(1/SUM(C8:C16)),"")
C34C34=IFERROR(1/(1/SUM(C19:C33)),"")
C37C37=IFERROR(1/(1/SUM(C35:C36)),"")
C39C39=IFERROR(1/(1/SUM(C17,C34,C37:C38)),"")
C41C41=IFERROR(SUM(C17,C34,C37)/C39,"")
C42C42=IF(C35="",C35,IF(C35>=0.1,"On Mnt's",IF(C35="","")))
C43C43=IFERROR(SUM(C17)/C39,"")
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
What about

Excel Formula: 
=SUM(C17,C34)-SUM(C37,C38)
 
Upvote 0
Solution
Hi Peter_SSs

Thanks so much it great and did exactly what i required thanks a million
 
Upvote 0
Hi Peter SSs

Thanks so much however after i insert the formula am having negative sign is the days vessel work and the percentage is above 100%

The Basic of this report is to fine following report monthly
Availability of Vessel in month
Productive time
Non Productive Time

Is there better way to go about this to have my result accurate report

Appreciate you support

Supply Vessels Utilization Reports 2021.xlsx
BCDEFGHIJKLM
7Supply Vessels Productive TimeADNOC-1010ADNOC-1011ADNOC-221ADNOC-222ADNOC-223ADNOC-224ADNOC-225ADNOC-226ADNOC-227ADNOC-228ADNOC-229
8Loading/ Offload ( Adnoc L&S Base )4.32.05.33.75.54.70.91.42.01.24.2
9Loading/ Offload (Free-Port) 0.1         
10Loading/ Offload Drilling / Artificial Islands5.24.26.40.43.34.00.2 0.11.00.2
11Loading/ Offload Jackup Rigs  3.815.05.36.323.52.619.216.718.2
12Loading/ Offload Barges    0.1      
13Loading/ Offload ( Complexes )           
14Loading/ Offload Prd-Island (DAS / Arzanah/ ZIRKU)0.1    0.0     
15Others / Marine/Rig Move           
16In Transit / Steaming to Location 12.812.49.96.69.38.74.02.48.38.65.3
17Sub Total 22.418.725.525.823.523.828.66.429.627.527.9
18Supply Vessel's Non-Productive Time
19Waiting Drilling Supply Material 0.1          
20Waiting For Adnoc L&S Base Operations Readines0.51.5 0.00.30.60.50.30.20.30.9
21Waiting For Free-Port/Mus-Port Readines 1.0       0.1 
22Waiting On VTS Permission 0.3  0.1 0.8 0.10.00.7
23Waiting On Channel Closure           
24Waiting Berthing Adnoc L&S0.80.50.40.31.50.7  0.10.1 
25Waiting On DayLight Complexes           
26Waiting On Prd-Island (DAS / Arzanah/ ZIRKU)     1.2     
27Waiting Instruction Rigs/Island  1.81.11.80.9     
28Waiting On Jackup Rigs      0.1 2.6   
29Waiting on Barges & Field Vessels           
30Waiting On DayLight / Artificial Islands/ Prd-Island1.31.81.1 2.21.80.2    
31Waiting Instruction Artifical Island3.35.6  0.51.0   1.1 
32Waiting On Weather ( Wind Speed / Sea Condition ) 1.60.51.30.00.3    0.80.3
33Waiting on Weather (Fog / poor visibility)           
34Sub Total 7.611.24.61.46.56.21.52.90.42.52.0
35Maintenance    2.8   20.6   
36Port Crew Change / Supply       0.1   
37Sub- TOTAL   2.8   20.8   
38Off-Hire           
39TOTAL Days30303024303030-12303030
40   2.8   20.8   
41Vessel Availability100%100%100%123%100%100%100%-261%100%100%100%
42   On Mnt's   On Mnt's   
43Productive Time75%63%85%106%78%79%95%-55%99%92%93%
Supply_Vessels_Utilization
Cell Formulas
RangeFormula
C38:M38,C35:M36,C19:M33,C8:M16C8=IFERROR(1/(1/SUMIFS(INDEX($BB$8:$BY$1547,0,MATCH($B$6,$BB$7:$BY$7,0)),$AZ$8:$AZ$1547,C$7,$BA$8:$BA$1547,$B8)),"")
C17:M17C17=IFERROR(1/(1/SUM(C8:C16)),"")
C34:M34C34=IFERROR(1/(1/SUM(C19:C33)),"")
C37:M37C37=IFERROR(1/(1/SUM(C35:C36)),"")
C39:M39C39=SUM(C17,C34)-SUM(C37,C38)
C40:M40C40=IFERROR(1/(1/SUM(C37:C38)),"")
C41:M41C41=IFERROR(SUM(C17,C34,C37)/C39,"")
C42:M42C42=IF(C35="",C35,IF(C35>=0.1,"On Mnt's",IF(C35="","")))
C43:M43C43=IFERROR(SUM(C17)/C39,"")
 
Upvote 0
Lukma said:
after i insert the formula am having negative sign is the days vessel work and the percentage is above 100%
Click to expand...
I don't know what that means. For your last mini-sheet, can you do the following?
- List the cells that have incorrect values
- List the correct values that should be in those cells & explain why those are the correct values.
 
Upvote 0
Dear Peter SSs

Thanks for you continues help

1. Column C row 17 = Time in Days
2 Column C row 34 = Time In Days
3. Column C row 37 =Time In Days Vessel not working
4. Column C row 38 = Time in Days Off-Hire

Now i need the Colum C Row 39 to Column AK row 39 to give me the numbers of Days Vessel have works which i need to Add the C17 & C342 and then Subtract the C37 & C38 to give me the numbers of days vessel worked in Month sample

TOTAL Days work 3030302730303093030303027302924
 
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
351
Lukma
L
L
  • Question
Need Help With a Formula reposting my Data
Replies
1
Views
359
KRice
K
B
  • Solved
Extract a range of values
Replies
8
Views
344
Fluff
Fluff
D
  • Solved
How to display record of last 10 games
Replies
5
Views
297
Eric W
Eric W
L
  • Question
Formula to check number of days productive and non Productive
Replies
2
Views
383
FMHasan
FMHasan

Forum statistics

Threads
1,214,979
Messages
6,122,560
Members
449,089
Latest member
Motoracer88

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