+ Reply to Thread
Results 1 to 22 of 22

Formula to convert kilograms to stones & pounds

  1. #1
    Registered User
    Join Date
    01-31-2007
    Posts
    12

    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. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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
    ---
    Si fractum non sit, noli id reficere.

  4. #4
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Thank you, all working fine now

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad you found an answer

    VBA noob

  6. #6
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    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. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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. #8
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    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. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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.

    ---
    Last edited by Bryan Hessey; 02-03-2007 at 06:53 PM.

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    All three do but Byran is more accurate

    57.13456 will return 8 st 14 lbs

    VBA Noob

  11. #11
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    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. #12
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Quote 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. #13
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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
    Last edited by VBA Noob; 02-03-2007 at 07:15 PM.

  14. #14
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Quote 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. #15
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try attached

    VBA Noob
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Sorry, on the file you sent and when i copy it into my spreadsheet, this message is displayed in the cell '#NAME?'

  17. #17
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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?

    On the Tools menu, click Add-Ins.
    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. #18
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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. #19
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    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. #20
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote 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. #21
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Thanks, that worked great! and thanks to everyone else who helped.

  22. #22
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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.

    added - and daddylonglegs

    ---
    Last edited by Bryan Hessey; 02-04-2007 at 04:46 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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