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.

 

[barry houdini]

Hello John, to show like 9st 03lbs etc. you could use

=INT(ROUND(A1*2.2046,0)/14)&"st "&TEXT(MOD(ROUND(A1*2.2046,0),14),"00\l\b\s")

 

[Richard Schollar]

John

Give this a try:

=INT(ROUND(A1*2.2046,0)/14)&"st "&TEXT(MOD(ROUND(A1*2.2046,0),14),"00\l\b\s")

 

[shippey121]

noticed that myself...as usual after i posted, anyway i changed it to

=INT(CONVERT(A2,"kg","lbm")/14)&"ST "&IF(ROUND(MOD(CONVERT(A2,"kg","lbm"),14),0)<10,0&ROUND(MOD(CONVERT(A2,"kg","lbm"),14),0)&" Lbs",ROUND(MOD(CONVERT(A2,"kg","lbm"),14),0)&" Lbs")

which also caters for the zero, bit long but it does the trick

HTH

 

[iggydarsa]

is it possible to get a zero before the pounds (when less than ten) just for neatness in the column.

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

 

[John North]

Hi, I've now got a spreadsheet that works and looks good too. I couldn't have wished for a better response. Thanks to everyone who helped me - iggydarsa, Richard Schollar, Barry Houdini, Shippey and VoG II.

John.

 

[Mike454]

Foolishly I forgot to look at the second page of this post, so I had a go myself and came up with this, it may be a little clumsy but is seems to work and give stones, pounds & ounces based on the value of cell A1 (Kilogram weight)

=QUOTIENT(A1,6.350288) & "st " & (QUOTIENT(MOD(A1,6.350288),0.453592)) & "lbs " & QUOTIENT(MOD(A1,0.453592),0.02835) &"ozs"

Mike