Pounds and Ounces subtraction formula
Results 1 to 5 of 5

Thread: Pounds and Ounces subtraction formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular redspanna's Avatar
    Join Date
    Jul 2005
    Location
    London
    Posts
    1,414
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Pounds and Ounces subtraction formula

    Hi all

    Through column B I have a simple list of numbers which represent pounds and ounces

    B5 = 144.2 (so this represents 144 pounds and 2 ounces)
    B6 = 138.9 (so this represents 138 pounds and 9 ounces)


    what formula could I use so that C1 shows the difference between B5 and B6 (and shown in lbs/ouz)


    example for above B5-B6, C1 would show answer as 5.9 , ie 5 pounds 9 ounces


    TIA

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,513
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pounds and Ounces subtraction formula


  3. #3
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pounds and Ounces subtraction formula

    This is a bit "schoolboy" but does the trick:
    In C1:

    =QUOTIENT((LEFT(MAX(B5,B6),FIND(".",MAX(B5,B6))-1)*16)+RIGHT(MAX(B5,B6),LEN(MAX(B5,B6))-FIND(".",MAX(B5,B6)))-((LEFT(MIN(B5,B6),FIND(".",MIN(B5,B6))-1)*16)+RIGHT(MIN(B5,B6),LEN(MIN(B5,B6))-FIND(".",MIN(B5,B6)))),16)&"."&MOD((LEFT(MAX(B5,B6),FIND(".",MAX(B5,B6))-1)*16)+RIGHT(MAX(B5,B6),LEN(MAX(B5,B6))-FIND(".",MAX(B5,B6)))-((LEFT(MIN(B5,B6),FIND(".",MIN(B5,B6))-1)*16)+RIGHT(MIN(B5,B6),LEN(MIN(B5,B6))-FIND(".",MIN(B5,B6)))),16)

    I'm sure there's a neater way of doing it, though ...
    Last edited by sykes; Jul 14th, 2019 at 08:15 AM.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,730
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pounds and Ounces subtraction formula

    Try one of the following

    Excel 2010
    BC
    15.9
    2895.9
    35.9
    4
    5144.22306
    6138.92217
    789

    2a



    Worksheet Formulas
    CellFormula
    B2=(INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10)
    C1=INT(B5)-INT(B6)-(MOD(B5,1)<MOD(B6,1))+((MOD(B5,1)<MOD(B6,1))*16+MOD(B5,1)*10-MOD(B6,1)*10)/10
    C2=INT(B2/16)+MOD(B2,16)/10
    C3=INT(((INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10))/16)+MOD(((INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10)),16)/10
    C5=INT(B5)*16+MOD(B5,1)*10


  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,463
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Pounds and Ounces subtraction formula

    Im curious how you deal with 10 ounce weights.

    As numbers, 3.1 = 3.10.
    So, when you convert these numbers to weights, does 3.1 represent 3 pounds 1 ounce or 3 pounds 10 ounces.

    (If Excel "sees" 3.1 is as text rather than numbers, this issue goes away. But others arise. Similarly, if you require the ounces to be expressed with two deicmal places (3 pounds 1 ounce being represented by 3.01) it also goes away.)
    Last edited by mikerickson; Jul 14th, 2019 at 12:21 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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