Convert pounds and ounces to decimal and other number questions

  • I am trying to type out and chart weights that are in pounds and ounces. I have already learned to custom format cells 0"lbs".00"oz" so 10.15 or 10lb 15oz shows as 10lbs.15oz

    Main issue with that is excel does not recognize the 15 ounces and 15 ounces but as 1.5 ounces and charts it as such which is a big issue when keeping track of ounces (it is a small animal)

    It is probably easier to convert to decimal or kg. Through this forum I have found this formula: =LEFT(A1, SEARCH(" ",A1,1))+(MID(A1,FIND(" lbs",A1)+2,3)) But that does not seem to work, at least not for something such as 1 lbs Am I typing it out wrong? There seems to be plenty of info of converting decimals to lbs and many other conversions but not this.

  • I suggest using two adjacent columns, one for pounds and one for ounces. You could concatenate these in a third column for display purposes.

  • But then how to chart that? I could use the separate columns for ounces, convert then add but it seems like there should be a far simpler solution (formula) instead of doing that.

  • yes, I just did that it was simple enough and completed what I basically wanted to do but I am annoyed there is no formula for everything except what I want! lol. It's like there is an app for everything except for what you actually need.

  • That could be simplified as =INT(E5)&""&MOD(E5,1)/.16 but I'm not sure what that gets you, as it has an embedded zero. Even if you remove that zero to make it convertible to a number, the number does not reflect reality. This is the best I can do, and would be usable with the ordinate axis of a graph (you have limited delimiter options, but could use a space, comma, dash, etc. in place of the ampersand):


  • Okay my formula still is flawed. 10lbs.15oz shows as 10.0.93700000000002 However 11lbs.8oz works. It comes out to 11.5
    Your formula: 10lbs.15oz shows as 100.937000000002. 11lbs.8oz comes out to 115.
    I know I am making this more complicated then it needs to be to find a simple solution but it's annoying me there has to be a simple or some sort of formula!

  • I thought the idea was to have the values readable as pounds and ounces on the ordinate axis of a chart. Use the actual values, e.g., 10 pounds 8 ounces is 10.5, then format the axis cells using the custom format from my above screenshot, where that would appear as 10 & 8/16.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!