Forum Discussion

Copper Contributor

Sep 30, 2021

Solved

Need help converting Inches to Gallons.

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

excel

mtarler
Oct 03, 2021
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.

NighT74

Copper Contributor

Oct 01, 2021

20,000 gallon tank is 10ft high and 34ft long.

mtarler

Silver Contributor

Oct 03, 2021

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.

NighT74
Copper Contributor
Oct 06, 2021
mtarler
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.