EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
143
I have a table where I record the start time and end time for Frac and Wireline operations on a well, and that process is repeated for each stage of the well. Sometimes we skip stages and no times are entered indicating that skip which messed up the Total Time calculation.

I left "Stage 3" (Row 112) blank to show what happens if we skipped that stage and moved on to "Stage 4" (Row 113). How can the formulas in cell I110 and on down the sheet be modified so it would omit the blank cell in the continue to add the times as we progress down the sheet not giving an error when a gap like shown occurs?

For appearance reasons, I would also like to cells below the last populated one in column I to remain blank until a value is calculated in Column H. For example, only calculate cell I115 when H115 is calculated with a value and so on down the sheet.

What that explained clearly?

COT Frac Report (REVISING).xlsm
ABCDEFGHI
107Stage TimesWest QALast Stage #:37
108Stage #Frac OpenFrac CloseFrac Stage TimeWL OpenWL CloseWL Stage TimeStage TimeTotal Time
109TOE1:002:451:4501:451:45
11012:504:151:251:232:451:222:474:32
11125:507:301:404:205:341:142:547:26
112300  
11349:3011:452:157:509:151:253:40#VALUE!
1145011:5513:451:501:50#VALUE!
115600  
116700  
117800  
118900  
1191000  
1201100  
1211200  
1221300  
1231400  
1241500  
1251600  
1261700  
1271800  
1281900  
1292000  
1302100  
1312200  
1322300  
1332400  
1342500  
1352600  
1362700  
1372800  
1382900  
1393000  
1403100  
1413200  
1423300  
1433400  
1443500  
1453600  
1463700  
1473800  
1483900  
1494000  
1504100  
1514200  
1524300  
1534400  
1544500  
1554600  
1564700  
1574800  
1584900  
1595000  
1605100  
1615200  
1625300  
1635400  
1645500  
1655600  
1665700  
1675800  
1685900  
1696000  
1706100  
1716200  
1726300  
1736400  
1746500  
1756600  
1766700  
1776800  
1786900  
1797000  
1807100  
1817200  
1827300  
1837400  
1847500  
1857600  
1867700  
1877800  
1887900  
1898000  
1908100  
1918200  
1928300  
1938400  
1948500  
1958600  
1968700  
1978800  
1988900  
1999000  
2009100  
2019200  
2029300  
2039400  
2049500  
2059600  
2069700  
2079800  
2089900  
20910000  
210Frac Total Hrs:7:05WL Total Hrs:5:51Total12:56
Stage Times
Cell Formulas
RangeFormula
D107D107=IF('Frac Report'!C17="","",'Frac Report'!C17)
I107I107=IFERROR(IF(COUNTIF('Frac Report'!F17:DB17,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F17:DB17,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F17:DB17,0))),"")
H109:H209H109=IF(SUM(D109,G109)=0,"",D109+G109)
D109:D209,G109:G209D109=IF(C109="","0",(MOD(C109-B109,1)))
D210,G210D210=SUM(D109:D209)
I109I109=H109
I110:I209I110=IF(H110="","",(H110+I109))
I210I210=SUM(D210,G210)
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(H110="","",sum(H110,I109))
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
143
How about
Excel Formula:
=IF(H110="","",sum(H110,I109))
I tried that in I113 for example where
Excel Formula:
=IF(H113="","",sum(H113,I112))

All it did was give the result of H113 because there was nothing in I112. I would like it to add H113 to, in this case I111, or what ever is the next cell above that has data in it. Otherwise my total time does not add up like I mentioned in the original post.

How would I do it so that:
If both cells C112 and F112 are blank, the enter I111, else SUM(H112,I111).
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Good point, how about
Excel Formula:
=IF(H110="","",SUM(H$109:H110))
 
Solution

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
143

ADVERTISEMENT

Good point, how about
Excel Formula:
=IF(H110="","",SUM(H$109:H110))
Closer. how would I write a formula for this:

Formula for Cell H110:
First, If C110 and F110 are BLANK, do nothing, then second, if SUM of D110 and G110 = 0, Return "0:00".
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't that formula work?
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
143
In what way doesn't that formula work?
Well it does work actually. I just didn't carry the formula down the column and test a few scenarios. It looks like it should be good to go now.

Thank you for your assistance!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,155
Messages
5,722,818
Members
422,460
Latest member
VBA_Noob01

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
Top