Formula to convert kilograms to stones & pounds

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

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

Glad you found an answer

VBA noob

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

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.

---