+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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