Converting from kilos to stones and pounds

[John North]

Hi, I'm trying to write a spreadsheet that compares body weights for older people in the UK who are not at ease with metric figures. I've searched the board and have come up with several answers, but they don't seem to work for me.

For instance, cell A1 is kilos, and I'd like that weight in stones and pounds in cell A2. What I've come up with for cell A2 is this:

=ROUND((A1*2.2046)/14,)&"st " &ROUND(MOD((A1*2.2046),14),0)&"lbs"

This works for, let's say, 90.8kgs in A1 (answer: 14st 4lbs), but if I enter 87.6kgs, for example, I get an answer of 14st 11lbs. I think it's something to do with the "ROUND" function but I'm struggling. Any help would be much appreciated. Thanks.

By the way, for non-Imperial weight people, multiplying by 2.2046 converts kilos to pounds, and there are 14 pounds (lbs) in a stone. Thanks again.

 

[iggydarsa]

give this a try:

=ROUND((A1*2.2046)/14-0.5,)&"st " &ROUND(MOD((A1*2.2046),14),0)&"lbs"

 

[Richard Schollar]

This works for me:

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

 

[John North]

Thanks, iggydarsa.

This works but I'm still struugling to see why. Now another anomaly has now popped up. If I put 76kgs into A1, I get the result 11st 14lbs, which is of course 12 stone.

I think I'm going to have to attack this problem from a different direction, but I'm only starting in what for me are some of the more complicated functions in Excel, and I can't get a simple, clean formula for this conversion. It's easy to see why the world prefers metric, but some of my elderly subjects just don't see metric - it means nothing to them in terms of comparison for weight and diet.

I've been stuck with this problem for several days now so I'll persevere - any help very gratefully received. Thanks.

 

[barry houdini]

Hello John, iggydarsa, Richard.

I think iggydarsa's suggestion can give odd results, e.g. if A1 = 76 (kg) then the result will be "11st 14lbs". Also, Richard's is rounding the lbs down so 68 kilos becomes "10st 9lbs" when it's just short of "10st 10lbs". I'd use

=INT(ROUND(A1*2.2046,0)/14)&"st "&MOD(ROUND(A1*2.2046,0),14)&"lbs"

 

[shippey121]

why dont you use the built in function to do it,

try

=CONVERT(A1*14,"lbm","kg")

HTH

 

[shippey121]

sorry wrong way round

try

=CONVERT(A2,"kg","lbm")/14

HTH

 

[John North]

Hello Iggydarsa, Richard, & Barry,

Thanks for your help and such a quick response.

I've now got a spreadsheet that works! Just one last thing, Barry - I hope I'm not pushing my luck - is it possible to get a zero before the pounds (when less than ten) just for neatness in the column. I know I should really study this myself, but it's the end of a long day and my brain's stopped working properly. Thanks.

 

[John North]

Hi Shippey,

I started with this problem with something like your suggestion, but it produces a decimal for the pounds so I was still stuck with the problem of converting a decimal fraction of a stone into pounds. I wish it had been that easy, Thanks.

 

[VoG]

With decimal pounds in B1

=INT(B1/14) & " St " & INT(MOD(B1,14)) & " lb(s)"