+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 22 of 22

Formula to convert kilograms to stones & pounds

  1. #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?'

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


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

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

  5. #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")

  6. #21
    Registered User
    Join Date
    01-31-2007
    Posts
    12
    Thanks, that worked great! and thanks to everyone else who helped.

  7. #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.
    Si fractum non sit, noli id reficere.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

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