Sep 30 2021 12:06 PM
Hi,
I'm trying to code excel to convert inches to gallons and needs to be able to handle fractions too.
Here are the specs on the tanks (keep in mind these are horizontal tanks with flat ends).
20,000 gallon tank (I dont have any measurements for the tank other than the tank chart that says it goes to 1 inch = 25.50 to 120 inches = 19975.60 gal.)
12,000 gallon tank 96"x32' 0" 1inch = 22 gal to 96 = 12077 gal.
Any help would be greatly appreciated.
Oct 01 2021 08:23 AM
Oct 01 2021 04:02 PM
Its the tank in the far right of the picture, the biggest one. Its 120 inches max capacity for the 20,000 gallon tank.
The 12,000 gallon tank is 96" high and 32' long. I have a 96" Diameter Guage chart for horizontal tanks.
We have always done the conversion by calculator and I want the employees to be able to input the inches in excel and the total made for them.
I am having someone measure the 20000 gallon tank length now. I hope this helps.
Oct 01 2021 05:19 PM
Oct 03 2021 03:53 PM
Solution@NighT74 so my calculations differ from your numbers slightly. The formula is:
=B1/231*IF(B3>B2/2,PI()*B2^2/4-ACOS(2*B3/B2-1)*B2^2/4-(B2/2-B3)*SQRT(2*B2/2*B3-B3^2),ACOS(1-2*B3/B2)*B2^2/4-(B2/2-B3)*SQRT(2*B2/2*B3-B3^2))
where B1 is the Length in inches, the /231 converts from cu.in. to gal. then the IF() statement checks if the height is > 1/2 way or not.
both of the formulas are based on finding the volume of a segment of a circle which is the area of a "slice of the pie" minus the triangle above it. and if the height is > 1/2 then 'flip it upside down' and subtract that from the total area.
In any case I attached the sheet and hope it helps but can't explain why my calculated volumes differ from your tables unless there is something else to consider.
Oct 06 2021 03:58 PM
Awesome, thank you so much. It works perfectly, I could not figure the formula out on my own literally frustrating me since I am not good with math.
Oct 03 2021 03:53 PM
Solution@NighT74 so my calculations differ from your numbers slightly. The formula is:
=B1/231*IF(B3>B2/2,PI()*B2^2/4-ACOS(2*B3/B2-1)*B2^2/4-(B2/2-B3)*SQRT(2*B2/2*B3-B3^2),ACOS(1-2*B3/B2)*B2^2/4-(B2/2-B3)*SQRT(2*B2/2*B3-B3^2))
where B1 is the Length in inches, the /231 converts from cu.in. to gal. then the IF() statement checks if the height is > 1/2 way or not.
both of the formulas are based on finding the volume of a segment of a circle which is the area of a "slice of the pie" minus the triangle above it. and if the height is > 1/2 then 'flip it upside down' and subtract that from the total area.
In any case I attached the sheet and hope it helps but can't explain why my calculated volumes differ from your tables unless there is something else to consider.