convert kilograms to stones AND pounds

[Bristlepete]

Converting Kilograms to Stones is straight forward but is it possible to
take the part of the number after the decimal point and convert it to pounds?

 

[Richard Schollar]

Hi

If you have a kilo weight in A1 then essentially your formula is:

=A1*2.2/14

to convert to Stones.

Apply a custom format of:

0 ?/14

will then give yo a return like:

10 5/14

meaning 10 stones, 5 pounds.

This what you wanted?

Richard

 

[David Biddulph]

=INT(CONVERT(A5,"kg","lbm")/14)&" st
"&ROUND(MOD(CONVERT(A5,"kg","lbm"),14),0)&" lb"

 

[Gord Dibben]

This formula will give you stones and pounds from Kg

=INT(A3*0.071429)&" Stones "&ROUND((A3*0.071429-INT(A3*0.071429))*10,0)&"
lbs"


Gord Dibben MS Excel MVP

 

[Chip Pearson]

The following formula will return a string like "10 Stones 5 Pounds"
where cell A2 contains the weight in Kg.

=TRUNC(A2*2.2/14)&" Stones"&A2*2.2-(TRUNC(A2*2.2/14)*14)&" Pounds"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sandy Mann]

Gord,

=INT(A3*0.071429)&" Stones "&ROUND((A3*0.071429-INT(A3*0.071429))*10,0)&"
lbs"

Should you not have multiplied by 14 instead of 10? (Unless of course
Canada has metric stones <g>)

and would it not be better to divide by 14 then multiply by 0.071429?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Chip,

Would it not be better to ROUND() or TRUNC() the pounds because you formula
returns

10 Stones 5.20000000000002 Pounds

for 66 Kg

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Chip Pearson]

Would it not be better to ROUND() or TRUNC() the pounds because you formula

returns

Yes, it would be better. Thanks for the save.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

Thanks Sandy

Typo on the 10

Not sure what you mean by the second part.


Gord

 

[David Biddulph]

An improvement to that:
=ROUND(CONVERT(A5,"kg","lbm")/14,0)&" st
"&MOD(ROUND(CONVERT(A5,"kg","lbm"),0),14)&" lb"

My original gave 10st 14 lb for 69.8 kg, for example, whereas my new formula
gives 11st 0 lb.

 

[David Biddulph]

.... and even more interestingly:
11 Stones-1.98951966012828E-13 Pounds for 69.9999999999999 kg

 

[David Biddulph]

As one example, with the 10 to 14 correction, 69.8 kg gives 4 Stones 14 lbs,
which doesn't seem quite right?
I guess that you are just converting from pounds to stones, rather than from
kg?
You did, however fall into the same 14lb trap that my earlier formula did,
which I've subsequently corrected by doing the rounding *before* the INT and
MOD [or INT and (x-INT(x))].

I think what Sandy was trying to suggest was that a division by 14 is exact
(subject to the precision of Excel's calculation, whereas your
mulitplication by 0.071429 is an approximation for the recurring number
0.07142857142857142857...).

 

[James Silverton]

As one example, with the 10 to 14 correction, 69.8 kg gives 4 Stones
14 lbs, which doesn't seem quite right?
I guess that you are just converting from pounds to stones, rather
than from kg?
You did, however fall into the same 14lb trap that my earlier formula
did, which I've subsequently corrected by doing the rounding *before*
the INT and MOD [or INT and (x-INT(x))].

I think what Sandy was trying to suggest was that a division by 14 is
exact (subject to the precision of Excel's calculation, whereas your
mulitplication by 0.071429 is an approximation for the recurring
number 0.07142857142857142857...).

Thanks Sandy

Typo on the 10

Not sure what you mean by the second part.

Gord

An interesting discussion but I wonder where in the world the
calculation is now needed? In any case, I remember that weights were
verbalized as, say, "10 stone 12" for a personal weight of 152 lb.

 

[Gord Dibben]

Yes, my formula converts from pounds to stones.

I used a formula I had cobbled together years ago.

Forgot what I was doing as usual.

Please disregard all my input to this thread.


Gord