Converting weights

**burnsie** · 02-04-2010, 05:59 AM

hello.

I'm just producing a weight conversion chart in Excel 2007

Is there any way of convert Lbs or Kgs into Stones/lbs?

I.e. to get a value of 12St 12lbs etc

The obvious problem is that if you apply a calculation;

Stones = Lbs / 14 then you get an answer of 12.8, 12.9 etc but can’t get to 13lbs in a stone.

Can this be changed in Excel?

Many thanks

B

**martindwilson** · 02-04-2010, 06:25 AM

depends on accuracy you require
this will do it to nearest pound
=INT(CONVERT(A1,"kg","lbm")/14)&"st "&ROUND((MOD(CONVERT(A1,"kg","lbm")/14,1) *14),0)&"lb"

**DonkeyOte** · 02-04-2010, 06:34 AM

I'm not quite sure what you're looking to do exactly but just on the off chance...

If you have say a Lbs figure - eg: 180 and wish to see that in stones.lbs then you can use DOLLARFR, eg:

=DOLLARFR(A1/14,14)

which would display as 12.12

I suspect Martin's suggestion of CONVERT is the way you'll want to go but the above function is handy to know if you're working with odd base terms.

Note: DOLLARFR requires activation of Analysis ToolPak pre XL2007

**burnsie** · 02-04-2010, 09:33 AM

Originally Posted by martindwilson

depends on accuracy you require
this will do it to nearest pound
=INT(CONVERT(A1,"kg","lbm")/14)&"st "&ROUND((MOD(CONVERT(A1,"kg","lbm")/14,1) *14),0)&"lb"

well, it that works....kinda

70kg = 11st (ish) but your formula brings it out at 11st 6lbs