Ounces to Gallons

[Holganix]

I need a formula that converts ounces into gallons that show the remainder not as a decimal but the remainder as ounces.

the formula that I am using =convert(A1,"oz","gal") works great but leaves me with a decimal. 7.6 I need it to read 7 gal 77 ounce

 

[doublej_jj]

Assume your 7.6 is in cell b1

= Trunc(B1) & " gal " & Round((B1-Trunc(B1))*128,0) & " ounce"

Notes:
Trunc (B1) gives your the 7 from 7.6

B1 - Trunc(B1) gives you just the decimal part of .6

Multiple the decimal part by 128, total ounces in a gallon, to get the ounce part

Hope that helps.

Jeff

 

[Weaver]

Are we talking US?

If so:

=INT(A1/128)&" gallons " & ROUND(MOD(A1,128),0) &" ounces"

 

[markmzz]

Holganix,

Or try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Ounces</TD><TD>Gallons</TD><TD>Ounces</TD><TD>Gallons & Ounces</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">973</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">77</TD><TD>7 gallons and 77 ounces</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>*******</TD><TD>*******</TD><TD>*******</TD><TD>*******</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=INT(CONVERT(A2,"oz","gal"))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=MOD(A2,CONVERT(B2,"gal","oz")/B2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=INT(CONVERT(A2,"oz","gal")) & " gallons and " & MOD(A2,CONVERT(B2,"gal","oz")/B2) & " ounces"</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[jim may]

Holganiz,

Please DO NOT Double-post.

 

[jasonb75]

Duplicate thread

http://www.mrexcel.com/forum/showthread.php?t=552041

 

[Jon von der Heyden]

Hi

Duplicate threads, such as this one, will be locked or deleted, per the forum rules. Please pursue the original thread referenced above.