Tank Calculation

[america05]

Im new to this. But here's my question. Im trying to make a spreadsheet where I can put in Feet in one cell, Inches in the other and have it calculate the gallons. Its a horizontal tank with cylindrical end caps, with a total of 62,926 gallons at 10'8 7/8". has any one done this? As of right now, I have to look it up on a printed strap sheet, I would like to just type it in and have it calculate it if all possible. Thanks for the help.

 

[DRSteele]

I sincerely hope you're not asking us to do your trig homework. Here is the answer nonetheless.

The example I created determines the volume of water in the horizontal cylinder where the height of the water is measured at 5'6 1/8" [Note that the column 'fraction' is numbers formatted as 'Fractions up to two digits'.]

references: Horizontal Cylindrical Segment -- from Wolfram MathWorld
Calculate volume in a horizontal cylinder or a cylindrical tank
http://www.handymath.com/cgi-bin/circlevali25.cgi

	A	B	C	D	E	F	G
capacity	gallons	cubic inches
conversion	gallon	cubic inches
feet	inch	fraction	inches	gallons	cubic inches
diameter
radius
length
measurement
volume

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"] 62,926.00[/TD]

[TD="align: right"] 14,535,906.00[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] 1.00[/TD]

[TD="align: right"]231[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"] 7/8[/TD]
[TD="align: right"] 128.87500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 5[/TD]
[TD="align: right"] 4[/TD]
[TD="align: right"] 7/16[/TD]
[TD="align: right"] 64.43750[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 92[/TD]
[TD="align: right"] 10[/TD]
[TD="align: right"] 1/3[/TD]
[TD="align: right"] 1,114.33268[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #FCE4D6, align: right"]5[/TD]
[TD="bgcolor: #FCE4D6, align: right"]6[/TD]
[TD="bgcolor: #FCE4D6, align: right"] 1/8[/TD]
[TD="align: right"] 66.12500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A9D08E, align: right"] 32,511.98[/TD]
[TD="align: right"] 7,510,266.54[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=B1*D2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=B5*12+C5+D5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=INT(E6/12)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=INT(MOD(E6/12,1)*12)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=MOD(MOD(E6/12,1)*12,1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=E5/2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=INT(E7/12)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=INT(MOD(E7/12,1)*12)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=MOD(MOD(E7/12,1)*12,1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=B1*D2/(PI()*(E5/2)^2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=B9*12+C9+D9[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]=E7*(E6^2*ACOS((E6-E9)/E6)-(E6-E9)*SQRT(E5*E9-E9^2))/D2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G10[/TH]
[TD="align: left"]=F10*D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

That could a situation where it'd be neat to use scroll bars (as sliders) to change input values.

 

[america05]

Haha. No, not homework. I work for Gas Processing Plant, but when I got here they were still doing their reports all hand written. With the limited knowledge I have of the computer I've, re-done everything using computer and having it do most of the work. But Im not that smart with complicated formulas. So THANK YOU for your help, I'll try and implement this and make our life easier. Thanks!

 

[DRSteele]

You're welcome. I'm curious about whether or not this solution was appropriate for you, so let us know.

 

[america05]

I've had time to get back on the computer and input the calculations. The end gauge when typed comes up to 62,926, but anything else it gives a different gallon number than the one in strap sheet. So we tried to get any info off the tag on the tank, and what we got from it was that it's a 63,000 gallon cap. Shell thickness 0.9375, Head thickness 0.528, 131 7/8 OD x 94.8 OAL or something like that (hard to read). We can't physically measure it since it's heavily insulated (stores y-grade product). And my Sup just told me the strap sheet is the one that they always used but no clue how they came up with it. Go figure. So I don't know if the extra info we found will make an impact on the calculations. Although I believe that we you came up with is more accurate than what we got.

 

[DRSteele]

Accurate? It's just an estimate anyway. When someone reads a ruler on the side of a humungous tank and translates the measurement into an implied volume by using a either strap sheet or an Excel program, it's always going to be just an estimate. Then tank is probably not a perfect cylinder either, owing at least to the fact that the head is probably convex and not flat; also, the tank might be full of sediment or barnacles on the bottom and the sides may have deposits. The liquid stored in the tank might be affected by temperature, barometric pressure or humidity, affecting the true volume. The measurement itself is just an estimate. So I suggest that the Excel calculations are clearly just as good (or bad!) as the reference sheet's.

"Always used but no clue..." hah, union people!

Best of luck!

 

[america05]

Yeah, its a circus over here at times. Well thanks for your time! I appreciate it!!

 

[DRSteele]

You're welcome. It was fun.

 

[DRSteele]

Years later, I wonder about your tank measurement problem. How goes it?