Formula to calculate the inches given tank size, length, and diameter and gallons per inch

[ismailkhowaja]

I want to calculate inches for the number of gallons filled in a tank so I need a formula for that.

Tank size = 6000 Gallons
Size = 96 * 192
1 inch can have 16 gallons (I need a formula for that). For example, if my tank contains 1000 gallons, how many inches is that?

 

[ismailkhowaja]

There are still some issues, I am working on.
I will post an update within an hour.

If you can, please Add an example of like how 1000 gallons would be 22

 

[Jamie McMillan]

Hi,

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32
16	38	64	93	126	161	199	240	282	327	374	423	474	527	531	637	695	755	816	878	943	1008	1076	1144	1214	1285	1358	1431	1506	1581	1658	1736
	22	26	29	33	35	38	41	42	45	47	49	51	53	4	106	58	60	61	62	65	65	68	68	70	71	73	73	75	75	77	78
		4	3	4	2	3	3	1	3	2	2	2	2	-49	102	-48	2	1	1	3	0	3	0	2	1	2	0	2	0	2	1
				1.5 x (Inches)2 + 17.8 * (Inches)												(Inches-17) x 70 + 685
19.3	41.6	66.9	95.2	126.5	160.8	198.1	238.4	281.7	328	377.3	429.6	484.9	543.2				755	825	895	965	1035	1105	1175	1245	1315	1385	1455	1525	1595	1665	1735
1	2	3	4	5	6	7	8	9	10	11	12	13	14				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3.3	3.6	2.9	2.2	0.5	0.2	0.9	1.6	0.3	1	3.3	6.6	10.9	16.2				0	9	17	22	27	29	31	31	30	27	24	19	14	7	1

The 2nd common difference goes crazy at inches ,15, 16, 17, in red.

So a rough formula for Inches 1 - 14 is as before 1.5 x (Inches)2 + 17.8 * (Inches)

A rough formula for Inches 18 - 32 is as before (Inches-17) x 70 + 685

The 2nd formula produces a normal distribution curve, with the differences from the original data sheet; so more could be done.

Jamie

 

[jolivanes]

@igold Post #17

Still does not confirm the shape. To tell you the truth, all gasoline and diesel tanks that I dealt with, and that are hundreds over the years, always have been round tanks.
The majority standing up but smaller, and this is relative, laying down.
Either one can be calculated but if the OP is not willing to give us all the information, no use spending time on possibilities.

 

[jolivanes]

Re: Size = 96 * 192
I guess it is too much to ask to be more specific. Are the above measurements inches, feet, cm, meters or what.
On a horizontal round tank with dimensions of 96 inches by 192 inches, the capacity is 6016.19 gallons which is pretty close to what OP mentions.
However, at 1 inch fluid level, there will be 10.8 gallons. Not 16 as OP suggests.

 

[jolivanes]

Just read the "headline" What I asked for previously is not needed. My apologies.
"Formula to calculate the inches given tank size, length, and diameter and gallons per inch"

Make sure to check results as this is from an old workbook that I have never used and also never checked for accuracy.
Never heard back from the OP that it was presented to if it was right or not.

Set up your sheet as in picture.
Add two "Form Control" Buttons as shown in picture (or wherever).
Copy both codes and paste in a regular module (Module1 maybe).

This code works on the "Make List" Button.

Sub Make_List_Inch_Gal()
Dim a As Long, i As Long, j As Long
a = WorksheetFunction.Ceiling(Range("K1"), 5) \ 5    '<--- Backward slash to make result an integer in case there are decimals in cell K1
Application.ScreenUpdating = False
    With Range("A4:A" & 3 + Range("K1").Value \ 1)    '<--- Backward slash to make result an integer in case there are decimals in cell K1
        .Formula = "=Row()-3"
        .Value = .Value
        .Offset(, 1).Formula = "=(R1C7*(R1C11/2)^2)*(ACOS(1-(2*(RC1))/R1C11)-(SIN(ACOS(1-(2*(RC1))/R1C11))*COS(ACOS(1-(2*(RC1))/R1C11))))*0.004329"
        .Offset(, 1).Value = .Offset(, 1).Value
        .Offset(, 1).NumberFormat = "0.000"
    End With

    j = 4 + a
    
    For i = 4 To 13 Step 3
        Range(Cells(j, 1), Cells(j + a - 1, 1)).Resize(, 2).Copy Cells(4, i)
        j = j + a
    Next i
    
Range("A" & a + 4 & ":B" & Range("K1").Value \ 1 + 4).EntireRow.Delete Shift:=xlUp    '<--- Backward slash to make result an integer in case there are decimals in cell K1
ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion.Resize(, 14).Address 
Application.ScreenUpdating = True
End Sub

This code works on the "Clear List" Button.

Sub Undo_Inch_Gallons()
    Range("A1:N" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(3).Delete Shift:=xlUp
End Sub

 

[ismailkhowaja]

View attachment 71987

Just read the "headline" What I asked for previously is not needed. My apologies.
"Formula to calculate the inches given tank size, length, and diameter and gallons per inch"

Make sure to check results as this is from an old workbook that I have never used and also never checked for accuracy.
Never heard back from the OP that it was presented to if it was right or not.

Set up your sheet as in picture.
Add two "Form Control" Buttons as shown in picture (or wherever).
Copy both codes and paste in a regular module (Module1 maybe).

This code works on the "Make List" Button.

Sub Make_List_Inch_Gal()
Dim a As Long, i As Long, j As Long
a = WorksheetFunction.Ceiling(Range("K1"), 5) \ 5    '<--- Backward slash to make result an integer in case there are decimals in cell K1
Application.ScreenUpdating = False
    With Range("A4:A" & 3 + Range("K1").Value \ 1)    '<--- Backward slash to make result an integer in case there are decimals in cell K1
        .Formula = "=Row()-3"
        .Value = .Value
        .Offset(, 1).Formula = "=(R1C7*(R1C11/2)^2)*(ACOS(1-(2*(RC1))/R1C11)-(SIN(ACOS(1-(2*(RC1))/R1C11))*COS(ACOS(1-(2*(RC1))/R1C11))))*0.004329"
        .Offset(, 1).Value = .Offset(, 1).Value
        .Offset(, 1).NumberFormat = "0.000"
    End With

    j = 4 + a
   
    For i = 4 To 13 Step 3
        Range(Cells(j, 1), Cells(j + a - 1, 1)).Resize(, 2).Copy Cells(4, i)
        j = j + a
    Next i
   
Range("A" & a + 4 & ":B" & Range("K1").Value \ 1 + 4).EntireRow.Delete Shift:=xlUp    '<--- Backward slash to make result an integer in case there are decimals in cell K1
ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion.Resize(, 14).Address
Application.ScreenUpdating = True
End Sub

This code works on the "Clear List" Button.

Sub Undo_Inch_Gallons()
    Range("A1:N" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(3).Delete Shift:=xlUp
End Sub

Basically i need to know this calculator works i just found on the internet

Calculation of Height of Liquid in a Horizontal Cylindrical Container

 

[jolivanes]

You can check that out yourself. I am sure that it will give you a true answer.

 

[ismailkhowaja]

You can check that out yourself. I am sure that it will give you a true answer.

It does give me write answer. I need to put this in my sheet so i can calculate the height of filled volume in tank

 

[jolivanes]

You can't put that in your sheet. It is a website. You can put a hyperlink to that site in your sheet so when you click on the hyperlink the site will open up.
Copy the below hyperlink and put it in your sheet.

Calculation of Height of Liquid in a Horizontal Cylindrical Container

If you use what I suggested in Post #25 you can cross reference.

 

[AhoyNC]

This should do what you want.
First you need to calculate the volume

Then multiple the volume by 0.004329 which is the number of gallons in a cubic inch.
On the web site you referenced note that 16 gallons was 1.298 inches.
If you use the web site and put in 1 inch it returns 10.82 gallons.

In the example below you can just change the height in cell C1. I just added some other heights so you can check against the web site.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Diameter	Length	Height
2	96	192	1		2	3	4	5	6	10	12	22	55	96
3	Radius		Gallons
4	48		10.82		30.52	55.89	85.77	119.48	156.56	332.43	434.05	1039.96	3564.65	6016.18
Sheet1

Cell Formulas
Range	Formula
A4	A4	=A2/2
C4,E4:N4	C4	=$B$2*($A$4^2*(ACOS(($A$4-C$2)/$A$4))-(($A$4-C$2)*SQRT((2*$A$4*C$2)-C$2^2)))*0.004329