Convert Feet and Inches into Barrels

**siroky25** · 09-07-2010, 11:47 PM

Hello, I have got two colums one is b8 which is feet and one is c8 which is inches. I also have this day by day for example, b8 and c8 are day 1 and b9 and c9 are day 2. What i need is the for example, b8 is 5' and c8 is 1" then b9 is 5' and c9 is 3". I need to be able to enter that 2nd day in and then calculate the feet and inches into barrels which is 1 inch=1.16 barrels. This is kinda hard to explain but if there is anyone who is smarted than me and know what i am talking about. PLEASE HELP!!! THANKS IN ADVANCE

**Paul** · 09-08-2010, 12:16 AM

Hi again, Siroky.

So are you saying after day 2 you need to subtract Day1 from Day2 to get the difference, then calculate the number of barrels? e.g. Day1 is 5'1" and Day2 is 5'3", you need to determine that 2" is the difference, and ultimately return 2.32 barrels?

If that's not the case, please explain a little more, or provide a sample workbook with how your data is setup and showing your expected results.

**siroky25** · 09-08-2010, 12:21 AM

You are an absolute genious! Yes, that is exactly what i am trying to do. Day 1 is 5'1 and day 2 is 5'3 so in the day 2 column which would be cell f9 that i was talking about should come out to 2.32 just like you were saying! Can you please explain to me how to insert that formula please! Much help man!

**Paul** · 09-08-2010, 12:27 AM

If B8 contains the number 5, C8 the number 1, B9 the number 5, and C9 the number 3:

In F9 use:

=(((B9*12)+C9)-((B8*12)+C8))*1.16

**siroky25** · 09-08-2010, 12:40 AM

Man, Thank you so much! That did work, just one other question, I'm sorry for bugging you but on some of my wells that i have, Ive got 2 tanks. So in short, if i have b8 and c8 as feet and inches but ive also got another row right next for another tank which is d8 and e8. Is there any way to put a formula in just like the one you gave me but add both those tanks instead of adding just the b8 and c8? I really do owe you a million, god bless the internet! Thanks in advance

**siroky25** · 09-08-2010, 12:44 AM

Ok, just to clarify. b8 is 5 and c8 is 1 and the next day is 5'3 so it made 2.32...Now also day #1 is d8=6 and e8=3 and day 2 is b8=5 and c8=3 and d8=6 and e8=5 so together one tank made 2.32 and the other tank made 2.32 so in the column f9 it would be 4.64....you roger that? Sorry im confusing

**Paul** · 09-08-2010, 01:05 AM

So then, maybe:

=((((B9*12)+C9)-((B8*12)+C8))*1.16)+((((D9*12)+E9)-((D8*12)+E8))*1.16)

or slightly shorter, since you're always multiplying by 1.16:

=((((B9*12)+C9)-((B8*12)+C8)))+((((D9*12)+E9)-((D8*12)+E8)))*1.16

**siroky25** · 09-08-2010, 01:14 AM

YES! That is exactly what i need brother! And one more question and then i will be out of your hair, when i enter the numbers into the feet and inches column, it will come up with the correct number in say like f9. But then in the cell f10, it will be a huge negative number until i input the next days gauges but the next cell like f11 will be a huge Negative number then? Any idea why this is doing this? Thanks again budd.

**Paul** · 09-08-2010, 01:22 AM

To hide results for rows in which values have not yet been entered, use an IF statement to wrap the function I gave you. For example, in F9 put this formula and fill down as far as you need:

=IF(COUNT(B9:E9)<4,"",((((B9*12)+C9)-((B8*12)+C8))+(((D9*12)+E9)-((D8*12)+E8)))*1.16)

I think I missed a parenthesis on the earlier function, so be sure to use this one.

**siroky25** · 09-08-2010, 01:41 AM

Huh? That formula didnt work at all, it gave me an error then said it had a solution and gave me another formula. Is there a reason that you put some of your font in red? Do i need to do that to? Also, say i have 14'6" for day 1....well i have to get tanks hauled so then day 2 could possibly be hauled back down to a gauge of 2'10" for example. It is showing a majorly wrong number. Do you know what I am saying?

**siroky25** · 09-08-2010, 01:55 AM

I am also getting this message: the formula in this cell refers to a range that has additional numbers adjacent to it......Any help?

**Paul** · 09-08-2010, 03:10 AM

The message you're getting is fine. It's just Excel's way of saying "Hey, maybe you forgot to include some numbers in your formula near the ranges you specified."

As for an error in the formula? Seems to be working for me. The red part was just what I added to the previous formula to check to see if four values had been entered in the current row.

Finally, regarding when you have tanks hauled, it might have been good to know that originally. If the scope of the question keeps getting changed it will take a day to resolve instead of 10 minutes. I'd suggest you upload a sample workbook with data that represents what you normally have to work with. Also show expected results based on that data so I know if the formula is returning the proper values.

**siroky25** · 09-08-2010, 09:20 PM

Hey again Paul, Sorry for the inconvenience about the formula, I did not think about the hauling of tanks. I have attached my workbook, In my line of work I usually have 2 tanks that are for Oil and 1 fills up with fluid then i need it hauled and truck come out and pull fluid of the tank....my tanks are 14'6" tall and when they fill up they will be hauled back down to a gauge of say 2'10" for this case. In my workbook, I have gave you an example what a guarter of the month usually looks like for me. If you go down to day 6 and 7, there is an example and also day 11 and 12. If you could please help me with this, it would be much appreciated and again, I am very sorry for the inconvenience! Thanks, Kodi

**Paul** · 09-08-2010, 09:45 PM

Ok, try this in cell F9 and filled down:

=IF(COUNT(B9:E9)<4,"",IF(AND(((B9*12)+C9)<((B8*12)+C8),((D9*12)+E9)<((D8*12)+E8)),"",IF(((B9*12)+C9)<((B8*12)+C8),(((D9*12)+E9)-((D8*12)+E8))*1.16,IF(((D9*12)+E9)<((D8*12)+E8),(((B9*12)+C9)-((B8*12)+C8))*1.16,((((B9*12)+C9)-((B8*12)+C8))+(((D9*12)+E9)-((D8*12)+E8)))*1.16))))

This first checks to see if there are four values entered into B9:E9, B10:E10, B11:E11, etc. depending on the row of the formula. Then it checks to see if both tanks have lower levels than the previous row, and if so it returns a blank. Next it checks to see if only the first tank was hauled, and if so only returns the increase from the second tank. Next it checks to see if only the second tank was hauled, and if so only returns the increase from the first tank. If neither tank was hauled (both values from the current row are greater than both values, respectively, from the previous row, it sums both tanks.

Hope that helps.

**siroky25** · 09-08-2010, 09:57 PM

Thank you so much man, I really do appreciate this help, you should be getting paid for this kind of stuff! Thanks again and hope ya the best!

**siroky25** · 09-08-2010, 10:29 PM

Paul, I have one more question and then I will be completely done bud. If i need to create another thread I will. My question is, If i only have 1 tank, e.g the B and C column in my attachment and nothing in the D and E column. When i enter my gauges, it will not calculate the inches into barrels in the F column and I would use one of the formulas from above but we did not get the formula with the HAULING tanks issue figured out until this last formula, So what would I need to do if I do only have one tank. The B and C column. Thanks in advance !!

**siroky25** · 09-08-2010, 10:44 PM

Hello again Paul, I do have one more question. If i need to post another thread, I can do so for you. My question is this, if I am only using one tank and not two like the last formula you gave me was for 2 tanks. If I am using only one tank though, e.g. Only inputting gauge numbers in column B and C and having nothing in the D and E columns, nothing will show up in the F column(BBLS PRODUCED column)(Found in the attachment earlier) I would use one of the formulas you gave me earlier but we did not get the issue where tanks were being hauled until this last formula. Please get back to me and let me know what I need to do! Thanks, Kodi

**Paul** · 09-08-2010, 10:59 PM

For just one tank, in columns B & C, use

=IF(COUNT(B9:C9)<2,"",IF(((B9*12)+C9)<((B8*12)+C8),"",(((B9*12)+C9)-((B8*12)+C8))*1.16))

Convert Feet and Inches into Barrels

LinkBack

Thread Tools

Rate This Thread

Display

Convert Feet and Inches into Barrels

re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Re: Convert Feet and Inches into Barrels

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions