Adding columns

**thmehr** · 10-19-2011, 12:17 PM

Hi - I am new on this forum and I thank you all for any help you can give me. I am trying to build a worksheet for my job. I watch and record data on oil wells. I need to figure the amount of oil produced over a 24 hour period. This will involve me using multiply oil storage tanks. When the storage tanks get full, trucks come in and purchase the load. So, my oil storage tanks are always changing between full and empty. I was going to use a column for each tank as I record the heights or the oil level each hour. How or what function or functions would I use. I am not sure how to handle when the tanks get purchased and my level drops. My first attempt was subtracting this activity and messy up my total oil produced.

Oh, I do not know VBA so I hope I do not have to use that feature to solve this issue. Thanks for you help

Ted

**khamilton** · 10-19-2011, 12:21 PM

if you could mock up a small sample and attach it you will get better response.

**thmehr** · 10-19-2011, 03:52 PM

Originally Posted by khamilton

if you could mock up a small sample and attach it you will get better response.

HI Here is a daily sample of my spreadsheet that I am trying to figure out how to add the fluid that has filled up the three oil tanks and 1 water tank. I need to have this a continuous rolling calculation from day-to-day. As you can see, the level drops. This is when the fluid is sold and trucked offsite. This is where I have issues trying to figure out how to figure out this calculation. When I do a simply change in height calculation, the drop in the fluid height messes up my calculation. Any help you can guide me with is very much appreciated.
thanks
ted

**gmcconnell** · 10-19-2011, 06:29 PM

Ted,

I'm also in the oil & gas industry in the northern Marcellus, just curious, where are you located?

Also, just so I am clear as to what you are doing... you are flowing water into the water tank and oil into the oil tanks from one well. Every once in a while a tanker takes a load of either oil or water, correct?

Furthermore, I would consider changing the units on your spreadsheet to a more useable value such as barrels.

**shg** · 10-19-2011, 07:20 PM

In L30 and copy across

=SUMPRODUCT( (L7:L29 <> "") * (L7:L29 < L6:L28), L6:L28 - L7:L29)

That's the total amount removed, assuming (a) the reading prior to a removal is the value immediately prior, and (b) nothing is being added while the removal is being made.

**thmehr** · 10-19-2011, 07:56 PM

Hi I work in the Killdeer North Dakota. I watch or babysit oil wells on a 24 hour basis. Easy job for someone who does have a life.

Yes, my purpose of this form is to figure the barrels produced, but I have to enter in the actual gauged heights and work from those numbers. I get stuck when the tank is emptied. I am not sure how to figure that out with a simple excel formula. Thanks. Ted

**thmehr** · 10-19-2011, 09:05 PM

Originally Posted by shg

In L30 and copy across

=SUMPRODUCT( (L7:L29 <> "") * (L7:L29 < L6:L28), L6:L28 - L7:L29)

That's the total amount removed, assuming (a) the reading prior to a removal is the value immediately prior, and (b) nothing is being added while the removal is being made.

Thanks for your help, but the column(s) I am trying to complete are Q and P for oil produced and water produced between each semi-hourly reading. I need help on the difference or the change in the level. I can convert the difference to barrels and barrels per hour. There maybe times when two tanks need to figured into the same production time frame. Example, when a tank is switched at 2:30. Thus I have some prodution in tank A and tank B.

I thought I was a fair at programing excel spreadsheets, but this just has me stumped and is above my knowledge level and I cant find any help in my excel book.. I am just do not know how to figure this out when there are peroids of no readings and when the tank gets sold and the process starts over. I have tried using IF Statements function but it is not working.