Displaying imperial weights in Excel (2003).

  John B 12:26 23 Sep 2009

More specifically, how do I display stones and pounds?

For example 215 lbs = 15st 5lbs, but of course dividing 215 by 14 leads to 15.35714!

Is there a way to do this please?



  Picklefactory 12:47 23 Sep 2009

Do you have your source cell in lbs as you've shown above? Do you want to convert pounds to stones/pounds?

  John B 12:54 23 Sep 2009

Yes, that's right; source = lbs. I'd like stones and pounds displayed in the same cell e.g. 15.5 =15st 5 lbs if possible.

  Picklefactory 13:00 23 Sep 2009

Hmm and how do you want the result displayed? You can have a formula to convert 215 to 15st 5lb, but as the cell would then conatain a mixture of numbers and letters it would be difficult to use that result in further calculations. If you just want the weight displayed in st and lb then that is a simple formula.

If cell A1 = 215
then in cell A2 place
=INT(A1/14)&"st "&ROUND(A1/14-(INT(A1/14)),2)*14&"lb"

  Picklefactory 13:02 23 Sep 2009

Do you mean that you want to enter 215 into a cell and have it convert that value in the SAME cell to st lbs? If I am understanding that correctly if it could be done at all, I think that would require some VBA code which is beyond me, put others here might be able to help with that.

  John B 13:09 23 Sep 2009

=INT(A1/14)&"st "&ROUND(A1/14-(INT(A1/14)),2)*14&"lb" seems to do the trick so probably no need for the VBA route.

The cell displays 15st 5.04lbs. Is there a way to get it display precisely 15st 5lb? If not, no worries!

Thanks again


  Picklefactory 13:24 23 Sep 2009

Just spotted an error in my formula too. Try this (Sorry it's so clunky, more skilled Excellers might do a tidier version) This will also fix the decimal lbs

=INT(A1/14)&"st "&ROUND(ROUND(F7/14-(INT(F7/14)),10)*14,0)&"lb"

  Picklefactory 13:25 23 Sep 2009

Should just add, you can click/drag that formula down your column to populate further rows.

  Picklefactory 13:28 23 Sep 2009

FE Edit option please? ;o)

Of course it should be

=INT(A1/14)&"st "&ROUND(ROUND(A1/14-(INT(A1/14)),10)*14,0)&"lb"

I was working somewhere else on my sheet, sorry.

  John B 13:45 23 Sep 2009

Thanks Picklefactory.

That's spot on. Job done!

If you have the time, can you explain (in simple terms) how the formula works please?

Thanks again either way!

  VoG II 13:51 23 Sep 2009

... but here's an alternative

=INT(A1/14)&" st "&MOD(A1,14)&" lb"

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?