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.
tank volumes.xlsx9 KB

mtarler

Silver Contributor

Oct 01, 2021

Well..... the math doesn't seem to 'add' up. by "flat ends" I assume you mean the tank has vertical walls so 120 inches should be 120x larger than 1 inch but 25.5x120 = 3060 which isn't close to 19975.6 and the same goes for tank 2. Also for tank 2 you give dimensions of 96"x32' 0" but that doesn't make sense either. is that 96" dimension the height? I assumed those were length and width but that would mean 159.6 gal/in but that times 95" (the height between those measurements) is over 15,000 gal. so i'll ignore the dimensions on tank 2 as we don't have it for tank 1 either.
So my only guess is that the 1 inch value is given to take into account irregularities of the tank bottom so all the bolts and the slope of the tank floor and such results in a much smaller volume than the rest of the tank which mean means every inch ABOVE the 1 in would be
(19975.60-25.5)/119 => 167.65 gal/inch for tank 1
(12077 - 22)/95 => 126.89 gal/inch for tank 2
of course this is some assumptions and guesses. additional data points could confirm or help understand it better. for example if the tanks are prismatic and the walls slope outward and then also assuming the 1" value is a true value for that prism then the formula would be something like V = c1 * h * ( 1 + c2 * h ) and with 2 points and 2 unknowns we can find c1 and c2:
tank 1: V = 24.315434 * h * (1 + 0.0487166 * h)
tank 2: V = 20.9073465 * h * (1 + 0.0522617 * h)
And then finally, this is really not anything to do with Excel and more like math class.

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.
  tank volumes.xlsx9 KB
  - 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.
NighT74
Copper Contributor
Oct 01, 2021
mtarler

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.