+ Reply to Thread
Results 1 to 10 of 10

Converting Kgs to Sts, Lbs & Ozs

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    Dumbarton, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Converting Kgs to Sts, Lbs & Ozs

    I do not use spreadsheets often, so I am certainly a novice. I want to be able to convert Kilograms to Stones, Pounds and Ounces.
    The formula I’m trying to adapt only shows Stones and Pounds and I’m trying to understand the various steps so that I might add the ounces.
    This is the formula: =IF(A13="","",IF(ROUND(MOD(A13*2.203,14),0)=14,ROUNDDOWN(A13*(2.203/14),0)+1&" st 0 lbs",ROUNDDOWN(A13*(2.203/14),0)&" st "&ROUND(MOD(A13*2.203,14),0)&" lbs"))

    Breaking the whole down
    In the nested ‘IF’ function it would seem to be saying that the TEST would be that the remainder of the product of A13*2.203 divided by 14 and rounded to zero digits after the dec.point would equal 14.
    The only numbers I can get a ‘TRUE’ reading are: 38 ; 57 ; 108 ; 127 ( in the range 35 – 135Kgs) yet none of these numbers = 14 after applying the test.
    I know its not a coincidence that there are 14 pounds in a stone but I can’t make the connection. Any help would be appreciated and if I’m in the wrong category I apologise.
    Last edited by sonlyme; 05-31-2010 at 12:14 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Converting Kgs to Sts, Lbs & Ozs

    Maybe this alternative?

    =INT(A13*0.157473044418)&" st "&INT(MOD(A13*0.157473044418,1)*14)&" lbs "&INT(MOD(MOD(A13*0.157473044418,1)*14,1)*16)&" oz"
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Converting Kgs to Sts, Lbs & Ozs

    Hi, Or this:- caters when there are "Stones,Lbs,ozs or when Kgs produce only Lbs,ozs"
    Please Login or Register  to view this content.
    Regards
    Mick

  4. #4
    Registered User
    Join Date
    05-31-2010
    Location
    Dumbarton, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Converting Kgs to Sts, Lbs & Ozs

    Thank you NBVC and MICKG both formulas worked a treat .
    I will spend a fair bit of time analysing each to help my understanding of both.


    cheers
    Sonlyme

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting Kgs to Sts, Lbs & Ozs

    Hello sonlyme, good answers posted by MickG and NBVC but if you want to understand the process this is how I would go about it......

    Possibly best to convert to the lowest unit, i.e. assuming there are 35.274 ounces in a kilo (you might want to use more decimals depending on how concerned you are about accuracy) then you can use this formula to convert your kilo value to a whole number of ounces

    =ROUND(A13*35.274,0)

    Now you can use that formula as the basis for the stones/pounds/ounces calculation, for example as there are 14*16 ounces in a stone you can get stones by dividing the ounces by 14*16 = 224 and rounding down, i.e.

    =INT(ROUND(A13*35.274,0)/224)

    and then you can get pounds by taking the remainder when divided by 224, then dividing that figure by 16 and rounding down, i.e.

    =INT(MOD(ROUND(A13*35.274,0),224)/16)

    and then the ounces is the remainder when total ounces are divided by 16, i.e.

    =MOD(ROUND(A13*35.274,0),16)

    string all three together and you get this

    =INT(ROUND(A13*35.274,0)/224)&" st "&INT(MOD(ROUND(A13*35.274,0),224)/16)&" lb "&MOD(ROUND(A13*35.274,0),16)&" oz"

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Converting Kgs to Sts, Lbs & Ozs

    Or even in the fine Scots' Tradition of squeezing the last dram out of anything.

    Converts kgs to st lbs ozs and drams (1dram = 1/16oz)

    Please Login or Register  to view this content.

    Or being really mean!!!
    where in A1 there is the conversion value 2.20462262 that can be set to the degree of precision required.
    Please Login or Register  to view this content.

    This is handy for settling anglers' arguements about catch weights!!!

    Slainte
    Alistair
    Last edited by Marcol; 05-31-2010 at 12:49 PM.

  7. #7
    Registered User
    Join Date
    05-31-2010
    Location
    Dumbarton, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Converting Kgs to Sts, Lbs & Ozs

    Hi,
    thanks daddylonglegs for the breakdown it really helps with following the logic.
    Alistair - copying both your formulas didn't give any results but I will type them in. I will also enjoy working out each of the stages/functions

    Again, thanks to everyone

    Sonlyme

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Converting Kgs to Sts, Lbs & Ozs

    Put the formula in B2

    The formula will return an empty string if there is no value in A2

    Enter a value in A2

    The second formula needs a conversion factor in A1

    It returns an empty string if there is no value in A2
    Enter a value in A2 and it will return 0st 0lbs 0oz 0d if there is no value in A1

    Enter your value in A1 to the degree of precision you want

    min value say
    2.2

    or
    2.20462262 as many decimal points as you want

    Slainte
    Alistair
    Last edited by Marcol; 06-01-2010 at 06:04 AM.

  9. #9
    Registered User
    Join Date
    05-31-2010
    Location
    Dumbarton, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Converting Kgs to Sts, Lbs & Ozs

    Hi Alistair
    you were, of course, spot on. I hadn't paid enough attention to where I was placing the formula and therefore putting the Kg value(s) in the wrong cell(s). Excellent

    cheers

    Sonlyme

  10. #10
    Registered User
    Join Date
    11-20-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Converting Kgs to Sts, Lbs & Ozs

    This formulas above were useful, but how could they be edited so only Pounds & Ounces?

    Thanks in advance.

+ 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