Excel formula for converting Horizontal Cylindrical Tank in Gallons to Inches

[terry7777]

I'm looking for help with an Excel formula for converting gallons stored in a Horizontal Cylindrical Tank to Depth in Inches.

I have successfully created a formula to go in the opposite direction, but now I need to work it the other way.

I have verified the formula with Tank Volume Calculator

However, I'm not able to reverse engineer the formula to work to solve for Depth in Inches

Can anyone help? It would be greatly appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Horizontal Tank Chart				Slury Tank Chart
2		Length Inches	Diameter Inches	Depth Inches
3					Total Gallons							Total Gallons	134.69
4		246.25	101.25	4.5	134.69							Depth Inches
Slurry Tank Chart

Cell Formulas
Range	Formula
D4	D4	='DATA SHEET'!AD5
E4	E4	=LENGTH*(0.25*DIAMETER^2*ACOS(1-2*Depth/DIAMETER)-0.5*(DIAMETER-2*Depth)*SQRT(Depth*$C$4-Depth^2))/231
L3	L3	=E3
L4	L4	=D2

 

[CSmith]

Given AREA = M3*231/LENGTH , LENGTH = B4, and RADIUS = C4/2
Here is a solution: Find THETA
you could use the above to write a custom recursive user defined function (UDF) that calls itself until same value is reached.
Initial approximation for theta would be:
theta=(12*AREA/RADIUS^2)^(1/3)
x=2*AREA/RADIUS^2
2 arguments for the UDF would be:
f(theta,x)
x will no longer change but theta will get closer and closer to exact after each iteration I would say no more that 4 or 5 should be needed.
f_theta(theta,x) = theta-(theta-SIN(theta)-x)/(1-COS(theta))

final answer can then be reasoned with
DEPTH=RADIUS*(1-COS(f_theta(theta,x)/2))

 

[CSmith]

So here is the function and final:
=RADIUS*(1-COS(fTheta((12*AREA/RADIUS^2)^(1/3),(2*AREA/RADIUS^2))/2))

Function fTheta(theta As Double, x As Double)
    Dim tmp As Double
    tmp = theta - (theta - Sin(theta) - x) / (1 - Cos(theta))
    If Abs(theta - tmp) > 0.0001 Then
        fTheta = fTheta(tmp, x)
    Else
        fTheta = tmp
    End If
End Function

 

[CSmith]

Apologies forgot about the tank being more than 1/2 full

=ABS(RADIUS*(1-COS(fTheta((12*AREA/RADIUS^2)^(1/3),(2*AREA/RADIUS^2))/2))-((LENGTH*PI()*RADIUS^2/2)<M3)*C4)

 

[CSmith]

Opps mistake above...
Formula should have read:
=ABS(RADIUS*(1-COS(fTheta((12*AREA/RADIUS^2)^(1/3),(2*AREA/RADIUS^2))/2))-((LENGTH*PI()*RADIUS^2/462)<M3)*C4)

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3	Length	Diameter	Depth	Gal	Max Vol (Gal)	RADIUS	THETA	AREA	5134.69		Total Gallons	5134.69
4	246.25	101.25	4.5	134.69	8583.10	50.6250	0.8496	126.350822971	96.75		Depth Inches	96.75
Sheet1

Cell Formulas
Range	Formula
E4	E4	=ABS((LENGTH*(RADIUS^2)*(THETA-SIN(THETA))/462)-(Vmax)*(DEPTH>(RADIUS)))
F4	F4	=LENGTH*PI()*RADIUS^2/231
G4	G4	=DIAMETER/2
H4	H4	=2*ACOS(ABS(RADIUS-DEPTH)/RADIUS)
I4	I4	=E4*231/LENGTH
J4	J4	=ABS(RADIUS*(1-COS(fTheta((12*Aseg/RADIUS^2)^(1/3),(2*Aseg/RADIUS^2))/2))-(((Vmax/2)<J3)*C4))
M4	M4	=ABS(RADIUS*(1-COS(fTheta((12*Aseg/RADIUS^2)^(1/3),(2*Aseg/RADIUS^2))/2))-((LENGTH*PI()*RADIUS^2/462)<M3)*C4)

 

[AlanY]

you can try the built-in goal seek function

 

[JoseJ]

Opps mistake above...
Formula should have read:
=ABS(RADIUS*(1-COS(fTheta((12*AREA/RADIUS^2)^(1/3),(2*AREA/RADIUS^2))/2))-((LENGTH*PI()*RADIUS^2/462)<M3)*C4)

CSmith, thank you for this! I have been looking for a way to get Excel to solve for fluid height rather than volume/gallons. So far, everything that works has agreed perfectly with the volume calculator and charts I have been using.

I am * this close * to getting the fluid height calculation to work, but I need your help. When I enter the formulas for J4 and M4, I get an error that I think is coming from the "fTheta" in both places (pls see screenshot). After much tinkering I have not been able to resolve this. What do I need to do? Thanks...

 

[Dave Patton]

If you want a low tech solution, consider the following.

N.B. You stated that the formulas that you have is accurate.

Prepare a table with the values for the increments spanning the full height.
Use a lookup formula. One example XLOOKUP(M3,E21:E1032,D21:D1032,,1)

 

[pjmorris]

Apologies, but I can't help thinking that there must be a formula to calculate this directly - but its 40years since I studied maths, and I seem to have forgotten more than I remember. Not really a help, but I can't believe that a complex approach is required. I just need to revise quite a lot of maths!!!

Good luck

 

[JoseJ]

If you want a low tech solution, consider the following.

N.B. You stated that the formulas that you have is accurate.

Prepare a table with the values for the increments spanning the full height.
Use a lookup formula. One example XLOOKUP(M3,E21:E1032,D21:D1032,,1)

Dave, thanks for that suggestion. It may be what I ultimately end up doing, but I'm trying to avoid it. When I have tried this approach for another application (correlation of glycol freezing point and fluid dilution), the method hasn't interpolated between increments, it just jumps from one increment to the next. In order for this method to work the way I'd like, a very pretty detailed table very needed using over 1500 increments. For example, imagine the table with 1/16-inch increments for a tank 96 inches tall.

Tables are what I have been using up to now -- a table for each horizontal tank I'm working with. Pretty simple to just look up the gallons in the table and see the fluid height. The biggest drawback is using a different table for each size tank (I'm working with nine different lengths and five different diameters). Easy to use the wrong table. The advantage of using the formula in Excel is you simply enter the dimensions and the gallons and it provides you with the fluid height. One app for all the various tanks (thankfully of the same basic design). That's why I'm spending a little time pursuing this approach.

Thanks again, Dave. I may go ahead and try your suggestion while still looking for a way to get this approach working.