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

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

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

4. Thank you, all working fine now

VBA noob

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.

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

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

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.

---

10. All three do but Byran is more accurate

57.13456 will return 8 st 14 lbs

VBA Noob

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?

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

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

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.

15. Try attached

VBA Noob

16. Sorry, on the file you sent and when i copy it into my spreadsheet, this message is displayed in the cell '#NAME?'

17. Picked the wrong one to adapt then.

If your getting the #NAME? error this is because you need to install and load the Analysis ToolPak add-in.

How?

In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program

VBA Noob

18. or try

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

VBA Noob

19. Last thing is the problem I said about earlier, shown below:

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

I had this response but now sure what it means exactly.

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

I tried just applying the formula you just did for me to the kg difference cell but that gives strange results.

20. Originally Posted by britlizard
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'.
You can solve your first problem with a shorter formula (without any weights being shown as 10 st 14 lbs etc.), i.e.

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

where your weight in kgs, is in A1

for your second question, are E6 and E5 both weights in kg? Will E6 always be greater than E5? If not do you want to show a negative amount? If so then to show a negative amount when E5>E6

=IF(OR(E5="",E6=""),"",IF(E5>E6,"-","")&INT(ROUND(ABS(E6-E5)*2.204623,0)/14)&" st "&MOD(ROUND(ABS(E6-E5)*2.204623,0),14)&" lbs")

21. Thanks, that worked great! and thanks to everyone else who helped.

22. Originally Posted by britlizard
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'.
correct, formulae should not be done in a hurry, correctly it was

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

however, no doubt VBA Noob has already provided a working version.

---

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1