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

 

[Dave Patton]

Just a little additional information in case you decide to use a Lookup.

What precision do you require?
I create a range 96*100 and replicated the formula increasing the height by 0.01
I converted the range to values and named the range for the Lookup.

T202501a.xlsm
	A	B	C	D	E	F
1		Horizontal Tank Chart			Slurry Tank Chart
2		Length Inches	Diameter Inches	Depth Inches
3					Total Gallons
4		246.25	101.25	4.50	134.6922
5		246.25	101.25	96.00	8,413.76
6
7
8
9		Lookup
10			Gallons	Depth
11			0.45	0.1
12			8,413.76	96
13			400.00	9.39
7f

Cell Formulas
Range	Formula
E4:E5	E4	=B4*(0.25*C4^2*ACOS(1-2*D4/C4)-0.5*(C4-2*D4)*SQRT(D4*$C$4-D4^2))/231
D11:D13	D11	=LOOKUP(C11,Tank_A)

Named Ranges
Name	Refers To	Cells
Tank_A	='7f'!$E$20:$F$9619	D11:D13

 

[JoseJ]

For precision, I would prefer matching the measuring sticks we use, which are imperial, 1/16-inch. That's a lot of reference points. Four decimal places... I think I'll start small and increase the resolution over time. Thank you for this info, Dave! This is looking more and more like a viable option!

 

[CSmith]

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...

View attachment 121637

fTheta is UDF in the #3 post above.

 

[Dave Patton]

I revised the Lookup Range to increment by 1/16 inch; I initially used 1/100 inch.

T202501a.xlsm
	A	B	C	D	E
1		Horizontal Tank Chart			Slurry Tank Chart
2		Length Inches	Diameter Inches	Depth Inches	Total Gallons
3		246.25	101.25	96.0000	8,413.76
4		246.25	101.25	30.0000	2,128.62
5		246.25	101.25	0.0625	0.22
6
7
8
9		Lookup
10				Depth	Gallons
11				4.5000	134.69
12				96.0000	8,413.76
13				0.0625	0.22
14				50.0000	4,224.09
15				9.3750	398.94
7ff

Cell Formulas
Range	Formula
E3:E5	E3	=B3*(0.25*C3^2*ACOS(1-2*D3/C3)-0.5*(C3-2*D3)*SQRT(D3*$C$4-D3^2))/231
E11:E15	E11	=LOOKUP(D11,Tank_AA)

Named Ranges
Name	Refers To	Cells
'7ff'!Tank_AA	='7ff'!$F$23:$G$1558	E11:E15