Conversion Formula

Can you provide the data pattern of the gauge input in excel attached?

Seems the gauge measure input is not number, so a procedure to extract numbers from the gauge text may needed.

Also, I'm doubt of the 9-6 3/8 result provided(476156 gallons)
I would like to confirm, it is (9*12 + 6 + 3/8) / (1/16) * 248 = 453840 gallons right?

Re: Nels:

What I use is a strapping chart that will tell how much fuel is at any given gauge. Such as; at the gauge I provided the chart says there are 476156 gallons. That is where I am having trouble trying to formulate excel into arriving at the same gallons as the gauging on the strapping chart for this tank, or for any take for that matter. I am doing it manually by looking it up on a chart. Whereas, I'd like to have excel do it for me in a spreadsheet.

I'm not sure how to answer your question about providing "the data pattern of the gauge input in excel attached".

Re: FDibbins:

Yes, it is only one dimension; height of the fuel in the tank. I do not have a sample workbook. I have strapping charts that give volume based on the gauge. Without sounding condescending...a tape measure is dropped down into the fuel and the reading is recorded and measured against a strapping chart. Or as we call it, the fuel is hand gauged. I would agree that 1" is 3968 gallons. However, the strapping chart works in 16ths. So I would have to be accurate to a 16th in my formula. I would also agree that it is a simple math equation but breaking it down into what I need to multiply is the conundrum.

(9*12 + 6 + 3/8) / (1/16) * 248 = 453840 I believe you are on the right track. Is this the formula to insert into excel to see if it will work?

Hi sbell,

I had prepared a template attached
Conversion Formula.xlsx

As I'm not sure how you need to input value of gauge, I prepared 2 solutions here:
Solution A(Row3): Input total gauge link in inches in column D
Solution C(Row5): Input total gauge link in Feet/inch/ 1/16 inch in column A-C

Hope this can give you an idea of solution.
But not sure if suitable to your situation.

Hi Nels, I think you are on to something. However, I'd like to have the gauge reading in one cell instead of three. I would also like to round up on the barrels as well as the gallons so as to have a whole number. I want to be able to enter the gauge as it is read at the time of gauging e.g. if I get 16-0 (that would be straight sixteen feet) or 15-5 15/16ths, or 21-2 5/8ths. I want the formula to sort it out and convert it to gallons. Is this possible? Do I need to be more clear in how I write it for the formula? That is...do I need to say 16'-5 1/2", or some derivative of that for a formula to read it? I would also like to be able to see your formula when you have one completed. I want this information for accounting purposes so it has to be in a format that accounting would want i.e. as simple and straightforward as possible. I am already using their spreadsheet but I want it to formulate the gauging for me instead of entering by hand. Thank you so much for your help Nels. I will stay tuned for your reply. You are very close to having the perfect formula for my endeavor.

BTW Nels, I am on my laptop and it only has viewing of excel. It doesn't allow me to have full version of excel. When I get home I can reopen your excel spreadsheet and perhaps see more of what you have done. My laptop is dated. In fact it has the home version of windows xp. I know, a poor choice in laptops but in 2003 when I bought it I knew very little about what I was getting. Anyway...

Thank you Marcol. I looked at your spreadsheet and I see that the numbers do not equal what the strapping chart says for those heights...perhaps a straight calculation will not be the same as what the strapping chart says for every sixteenth, even though we have a 16th equaling 248 gallons. Can we not have the gauging match a chart of the values of the strapping chart itself? If so, would all the values of the strapping chart have to be created for the formula to reference it?

I've do some update to Marcol's formula(sorry Marcol), now you can input gauge in format either "#ft-#ins #/#" or "#ft-#ins".
Here is the update file, which the extra column C is the formula.
Daily_Gauging_Log3_v2.xls

As Marcol mentioned, there have more factors to affect the final gallon volume.
If they can be found out that's help to solve the case.

Originally Posted by Marcol

Looking at your "strapping chart", I think that there are more variables than you first suggested.

Temperature could well be a significant one.
Do you have expansion co-efficients for each tank and its' contents?

The only way to handle this with any surety will be to have a lookup table for every tank.

Yes Marcol, a lookup table for the spreadsheet would be awesome! How would I do that? If you don't mind? You have been so very helpful. Thank you.

Originally Posted by Nels

I've do some update to Marcol's formula(sorry Marcol), now you can input gauge in format either "#ft-#ins #/#" or "#ft-#ins".
Here is the update file, which the extra column C is the formula.
Attachment 188488

As Marcol mentioned, there have more factors to affect the final gallon volume.
If they can be found out that's help to solve the case.

Nels, I'm sorry but I don't see column C with the formula. I like how you converted the gauge into inches. But how do I convert the inches into gallons? For tank one I know a 16th of an inch is worth 248 gallons. Will this help? What I need is a lookup table for each tank. Also, I wanted to address Marcol's and yours concern about the tempature. The tempature is used for calculating net gallons by the accounting department and is also done on another form we provide. However, for this form they simply want gross gallons. They must figure the net with the information given. Thank you both so much for your patience and time with this issue.

Originally Posted by Nels

I've do some update to Marcol's formula(sorry Marcol), now you can input gauge in format either "#ft-#ins #/#" or "#ft-#ins".
Here is the update file, which the extra column C is the formula.
Attachment 188488

As Marcol mentioned, there have more factors to affect the final gallon volume.
If they can be found out that's help to solve the case.

OK, I see column C but no formula, only the results, Could be my laptop. Anyway, can we not create a formula without column C so that column B can be converted to inches and then into gallons? Thank you.

I will provide a copy of a page from the strapping chart for your review in a few hours when I get home.

Originally Posted by Marcol

Perhaps better you use this in C5, it will work for all versions of Excel, IFERROR() will only work for 2007 and above

Formula:

Please Login or Register  to view this content.

How many tanks are we talking about?

Are they all one size in height and diameter?

If not how many types are there?

If we had data for several days for one tank type we might be able to establish an empiric formula to handle height and temperature variations, and then set up a lookup table for that type of tank.

Marcol, we are talking about 18 tanks. All are different sizes. They are all above ground and round tanks. But hold different amounts of fuel and I have a strapping chart for each tank. If I could get the strapping charts onto excel and then reference them in my spreadsheet they would be ideal I think. Then there would be no calculations only a reference lookup.

Originally Posted by Nels

Hi sBell,

Sorry that the attachment was invalid to download(wield).It's now attached again.
The formula is in yellow highlighted.
Attachment 188504

(Inch) * 16 * 248 will be the gallon value. Here below is the gallon value calculated in column C:
Attachment 188509

HOWEVER, comparing the calculated value in column C and the provided value in column E they have major difference.
There should have something missing in the formula you provided.
This is what I'm concerning about.

Thanks Nels. I didn't see the formula but then again it could be my laptop. The value of 248 would only be valid with tank one. The other tanks have their own value for a 1/16th of an inch. If I can see the formula then I can put in the value for each tank into it. But again, I think my best option may be a lookup chart/reference for these tanks. If I can put in the value of the gauge as you read it without converting to inches and have excel look it up it would solve everything. Should I repost my question in excel as a lookup question? I appreciate your help and Marcols. Thank you.

The formula in cell C5 is as follows:

Please Login or Register  to view this content.

You can change the "248" in formula to the lookup value to table in another sheet.
If you can have a lookup chart.reference for all tanks and all their gauges that will be nice
, but I wonder if it is a gigantic chart to be prepared as there can be so many combinations?

Originally Posted by Marcol

I take it that they are all vertical tanks?

What does your strapping chart look like?
What format is it in?

Can't you put one into Excel and post it so we can see the information in it?
If it isn't in a format that the forum allows, zip it and then post it.

Let's get one into Excel as a lookup table one way or another.
Seems to me that this would be the only way to go.

Marcol, they are vertical tanks. I have a page out of a strapping chart I will post in about two hours. It is just a copy. Each tank has about 8 pages of gauging. So it would be labor intensive to put it into a excel format. You will see when I post it. Hopefully something can be figured out. Thanks for your posts.

Originally Posted by Nels

The formula in cell C5 is as follows:

Please Login or Register  to view this content.

You can change the "248" in formula to the lookup value to table in another sheet.
If you can have a lookup chart.reference for all tanks and all their gauges that will be nice
, but I wonder if it is a gigantic chart to be prepared as there can be so many combinations?

Yes, it would be gigantic. I think out of all these post by you and Marcol, that I may have to just look up the value of the gauge manually and enter it in myself. I may be trying to bite off more than I can chew. It was worth the effort. I'm not giving up yet. I think we will get this or die trying, lol. Thanks for all your post.

Nevermind sbell, the solution is now touchable for me.
I just need more information

Originally Posted by Marcol

Are you still using 2003 as your profile suggests?

The Morefunc add-in could be an advantage.

Try this workbook as the first step of an experiment.
Do you get the correct results in E5:E10? If not I have another step to try.

Yes MARCOL, I am using excel off office 2003.

Originally Posted by Marcol

Are you still using 2003 as your profile suggests?

The Morefunc add-in could be an advantage.

Try this workbook as the first step of an experiment.
Do you get the correct results in E5:E10? If not I have another step to try.

Marcol and NELS this is what a page out of the strapping chart looks like. Please see attached. I had to crop it because it was too big to upload. However, you can still what I am talking about.

It looks to me like we are missing one piece of information from all of this -- how many gallons are in the tank when the gauge reads 0. Everything I've seen so far has assumed that the tank holds 0 gallons when the gauge reads 0, but I'm beginning to doubt this assumption.

I entered a very small subset of the data from the strapping chart and regressed a straight line from that data. From this equation, I get, as indicated earlier, a slope of 3962 gal/inch (or 248 gallons per 1/16 inch). The equation also suggests that there are 23000 gallons in the tank when the gauge reads 0. Is this a possible scenario?

Assuming there are no "anomalies" in the gauge readings, I would suggest that the way I might approach this is to enter a suitable sample of data from the strapping chart, regress a straight line (in Excel, you can use the LINEST() function), then evaluate that function to make sure it is accurate enough at any gauge reading.

Originally Posted by Marcol

@ FDibbins
Calibrated strapping charts are not reliant on the plan shape of vertical tanks, this is factored in for each individual tank.
Tapers, and other geometric deviations, are also factored in as should any internal pumps, valve cases. etc.

What we need is to establish if a basic table and formula can be extracted from the chart. The charts might be "gigantic", but it may well be formulaic, so we might be able to use a relatively small table.

If the charts aren't available, they will have to be empirically deduced from as many records as we can have for any particular tank, MIT reckons on a minimum of 36 samples.

At the end of the day it is obviously far better to have the tanks recalibrated than using empirically deduced results.

@ sbell521
Did you get the correct results in E5:E10 in my last file?

Marcol, I have not had time to correct the results you are speaking of. I was at work last night and now I am home. It is time to get some sleep. It may have to wait until my day off to fully get involve. Anyway, the gauging must match the strapping charts I have because that is what accounting uses too. If we deviate even one digit then the formula will be void.

Originally Posted by MrShorty

It looks to me like we are missing one piece of information from all of this -- how many gallons are in the tank when the gauge reads 0. Everything I've seen so far has assumed that the tank holds 0 gallons when the gauge reads 0, but I'm beginning to doubt this assumption.

I entered a very small subset of the data from the strapping chart and regressed a straight line from that data. From this equation, I get, as indicated earlier, a slope of 3962 gal/inch (or 248 gallons per 1/16 inch). The equation also suggests that there are 23000 gallons in the tank when the gauge reads 0. Is this a possible scenario?

Assuming there are no "anomalies" in the gauge readings, I would suggest that the way I might approach this is to enter a suitable sample of data from the strapping chart, regress a straight line (in Excel, you can use the LINEST() function), then evaluate that function to make sure it is accurate enough at any gauge reading.

Mr. Shorty there is still a residual volume of fuel in the tank even if you get a 0 reading. That is because the bottom of the tank is concave and the 0reading goes straight across the tank in a level manner. If I could sketch it our for you that might help. I know you are thinking...'What do I gotta draw you picture of something?" lol. The remaining volume is already calculated in the figures by some department or other. Hope this helps.

Thanks to all who contributed to my post. My employer is going to update our computer to a new one with more memory. When and if this happens we will be able to load on the strapping charts of 19 tanks for use in excel. I believe they may already be on excel by our accounting department. When this happens I will post a new question about vlookup or lookup, or something like that so I can reference the charts with my gauging figures. Thank you again everyone! I will post this as solved.

Um...could someone tell me how to post this as solved? lol.

hi i am Magdy my problem is how to calculate the required crane capacity if you have the radius and the weight IF SOME BODY has excel sheet to calculate this exercise please upload it

thanks

magdy kamel

Thanks for Telling me. Sorry