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?

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

--
Richard Schollar

Microsoft MVP - Excel


"Bristlepete" wrote in message
...
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?

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

"Bristlepete" wrote in message
...
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?

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=INT(CONVERT(A5,"kg","lbm")/14)&" st
"&ROUND(MOD(CONVERT(A5,"kg","lbm"),14),0)&" lb"
--
David Biddulph

"Bristlepete" wrote in message
...
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?

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


On Mon, 27 Oct 2008 15:44:01 -0700, Bristlepete
wrote:

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?

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


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Mon, 27 Oct 2008 15:44:01 -0700, Bristlepete
wrote:

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?

Thanks Sandy

Typo on the 10

Not sure what you mean by the second part.


Gord


On Mon, 27 Oct 2008 23:55:12 -0000, "Sandy Mann"
wrote:

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?

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...).
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Thanks Sandy

Typo on the 10

Not sure what you mean by the second part.

Gord

On Mon, 27 Oct 2008 23:55:12 -0000, "Sandy Mann"

wrote:

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?

"David Biddulph" <groups [at] biddulph.org.uk wrote in message

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...).

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Thanks Sandy

Typo on the 10

Not sure what you mean by the second part.

Gord

On Mon, 27 Oct 2008 23:55:12 -0000, "Sandy Mann"

wrote:

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?

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.

--
James Silverton
Potomac, Maryland

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

On Tue, 28 Oct 2008 07:01:46 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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...).

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)


On Mon, 27 Oct 2008 15:44:01 -0700, Bristlepete
wrote:

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?

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


Replace @mailinator.com with @tiscali.co.uk


"Chip Pearson" wrote in message
...
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)


On Mon, 27 Oct 2008 15:44:01 -0700, Bristlepete
wrote:

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?

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)

On Mon, 27 Oct 2008 23:59:30 -0000, "Sandy Mann"
wrote:

Chip,

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

10 Stones 5.20000000000002 Pounds

for 66 Kg

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

"Sandy Mann" wrote in message
...
Chip,

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

10 Stones 5.20000000000002 Pounds

for 66 Kg

"Chip Pearson" wrote in message
...
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"

On Mon, 27 Oct 2008 15:44:01 -0700, Bristlepete
wrote:

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?