# Formula to convert kilograms to stones & pounds

1. ## Formula to convert kilograms to stones & pounds

Hello, could someone please give me a little help. What I want to do is convert a weight from kilograms to stones & pounds. So say in cell C5 I will have a weight in kilograms, say 72, then in cell C6 I want it to display that weight in stones & pounds, which should convert as 11 stone 5 pounds. I just need the formula that will do the conversion.

Thank you.  Register To Reply

2. Hi,

Try

=ROUNDDOWN(C5*(2.203/14),0)&" st "&ROUND(MOD(C5*2.203,14),0)&" lbs"

or

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

VBA Noob  Register To Reply

3. Originally Posted by britlizard
Hello, could someone please give me a little help. What I want to do is convert a weight from kilograms to stones & pounds. So say in cell C5 I will have a weight in kilograms, say 72, then in cell C6 I want it to display that weight in stones & pounds, which should convert as 11 stone 5 pounds. I just need the formula that will do the conversion.

Thank you.
Hi,

try

=INT(A1*2.204623/14)&"st "&TEXT(MOD(A1*2.204623,14),"#.0")&"lb"

hth
---  Register To Reply

4. Thank you, all working fine now   Register To Reply

VBA noob  Register To Reply

6. Sorry to be a pain, but I now have a couple of issues arising from the conversion formula, which I'm hoping can be resolved by you guys - please.

Right, the conversion works fine, but the column that I will be entering a weight (kg) into has empty cells and every week a weight will be entered, so as they stand most are blank, but if i copy the conversion formula into the st/lbs column, I end up with the column showing 0 st 0 lbs (as the cells they relate to are empty). Is there a way for the cell with the conversion formula in it to appear empty UNTIL the cell it relates to has had a number entered into it. I hope that all made sense!

The second problem I need help with is as follows: i have two cells showing the difference between a start weight and current weight. One shows the weight in 'kg', it was simple '=SUM(E6-E5)', but the other is the 'st/lbs' cell, is there a formula that can take the current weight 'cell F6' from the start weight 'cell F5' and display it in the format as the conversion formula does '12 st 1 lbs'.

Thanks for any help, I would be well stumped without you guys.  Register To Reply

7. Originally Posted by britlizard
Sorry to be a pain, but I now have a couple of issues arising from the conversion formula, which I'm hoping can be resolved by you guys - please.

Right, the conversion works fine, but the column that I will be entering a weight (kg) into has empty cells and every week a weight will be entered, so as they stand most are blank, but if i copy the conversion formula into the st/lbs column, I end up with the column showing 0 st 0 lbs (as the cells they relate to are empty). Is there a way for the cell with the conversion formula in it to appear empty UNTIL the cell it relates to has had a number entered into it. I hope that all made sense!

The second problem I need help with is as follows: i have two cells showing the difference between a start weight and current weight. One shows the weight in 'kg', it was simple '=SUM(E6-E5)', but the other is the 'st/lbs' cell, is there a formula that can take the current weight 'cell F6' from the start weight 'cell F5' and display it in the format as the conversion formula does '12 st 1 lbs'.

Thanks for any help, I would be well stumped without you guys.
for the first question,

=If(C1="","", the formula )

and insert the formula that works best for you,

for the second question the same, but refer to the new column in the If test and the formula.

hth
---  Register To Reply

8. One last thing, I see with the formula it displays '8 st 14 lbs', but as there are 14 lbs in a stone, shouldnt it only display up to '8 st 13 lbs' and then jump to '9 st 0 lbs'?  Register To Reply

9. Originally Posted by britlizard
One last thing, I see with the formula it displays '8 st 14 lbs', but as there are 14 lbs in a stone, shouldnt it only display up to '8 st 13 lbs' and then jump to '9 st 0 lbs'?
there were 3 formula, which one are you using?

mine did that (an after effect of the 'round), try

=INT(ROUND(A1*2.204623/14,1))&"st "&TEXT(MOD(ROUND(A1*2.204623,14),1),"#")&"lb"

I will be back after the cricket.

---  Register To Reply

10. All three do but Byran is more accurate

57.13456 will return 8 st 14 lbs

VBA Noob  Register To Reply

11. I used the first formula:

=ROUNDDOWN(E5*(2.203/14),0)&" st "&ROUND(MOD(E5*2.203,14),0)&" lbs"

as i dont want decimals on the pounds, just to the nearest whole pount '10 st 4 lbs' not '10 st 4.2 lbs'.

Should it not go from 10 st 13 lbs to 11 st 0 lbs?  Register To Reply

12. Originally Posted by Bryan Hessey
there were 3 formula, which one are you using?

mine did that (an after effect of the 'round), try

=INT(ROUND(A1*2.204623/14,1))&"st "&TEXT(MOD(ROUND(A1*2.204623,14),1),"#")&"lb"

I will be back after the cricket.

---

Thanks but this formula doesnt work right, if i put in '75.1' i get '11 st 1 lbs', then '75.3' gives '11 st lbs' - no number for pounds, and it keeps showing either a '1' for lbs or nothing until it gets to 75.9 then it jumps to '12 st lbs'.  Register To Reply

13. Try

=IF(C5="","",IF(ROUND(MOD(CONVERT(C5,"kg","lbm"),14),0)=14,INT(CONVERT(C5,"kg","lbm")/14)+1&" st 0 lbs",INT(CONVERT(C5,"kg","lbm")/14)&" st "&ROUND(MOD(CONVERT(C5,"kg","lbm"),14),0)&" lbs "))

VBA Noob  Register To Reply

14. Originally Posted by VBA Noob
Try

=IF(C5="","",IF(ROUND(MOD(CONVERT(C5,"kg","lbm"),14),0)=14,INT(CONVERT(C5,"kg","lbm")/14)+1&" st 0 lbs",INT(CONVERT(C5,"kg","lbm")/14)&" st "&ROUND(MOD(CONVERT(C5,"kg","lbm"),14),0)&" lbs "))

VBA Noob

Thanks, but it says there are errors with it.  Register To Reply

15. Try attached

VBA Noob  Register To Reply