Formula to convert Oz back into autosum and add to left of decimal place
Results 1 to 6 of 6

Thread: Formula to convert Oz back into autosum and add to left of decimal place
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Location
    Market Drayton,UK
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to convert Oz back into autosum and add to left of decimal place

    Help please,

    I have a fishing club workbook that I have to sit and work out in Lbs and Ounces.

    I have the format for cells as ustom 0" lb". 00" oz."

    Once autosum has been applied to a range it will give a total in LBs and Ounces but counts all of the ounces up.

    Is there a formula that anyone knows where I can automatically divide the ounces by 16 and any lbs moved to the left of decimal place and remaining ounces shown to the right.

    Final sum required would be 34lb. 6oz. if this makes sense


  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,858
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to convert Oz back into autosum and add to left of decimal place

    Here is a sample

    v A B C D
    1 Total Oz Lbs Oz Combined
    2 30 1 14 1lb 14oz
    3 17 1 1 1lb 1oz
    4 21 1 5 1lb 5oz

    and here are the formulas

    v A B C D
    1 Total Oz Lbs Oz Combined
    2 30 =INT(A2/16) =MOD(A2,16) =CONCATENATE(B2,"lb"," ",C2,"oz")
    3 17 =INT(A3/16) =MOD(A3,16) =CONCATENATE(B3,"lb"," ",C3,"oz")
    4 21 =INT(A4/16) =MOD(A4,16) =CONCATENATE(B4,"lb"," ",C4,"oz")
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    Sep 2012
    Location
    Market Drayton,UK
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to convert Oz back into autosum and add to left of decimal place

    [QUOTE=alansidman;5312925]Here is a sample

    v A B C D
    1 Total Oz Lbs Oz Combined
    2 30 1 14 1lb 14oz
    3 17 1 1 1lb 1oz
    4 21 1 5 1lb 5oz

    and here are the formulas

    v A B C D
    1 Total Oz Lbs Oz Combined
    2 30 =INT(A2/16) =MOD(A2,16) =CONCATENATE(B2,"lb"," ",C2,"oz")
    3 17 =INT(A3/16) =MOD(A3,16) =CONCATENATE(B3,"lb"," ",C3,"oz")
    4 21 =INT(A4/16) =MOD(A4,16) =CONCATENATE(B4,"lb"," ",C4,"oz")

    Alan,

    Many thanks for the formula which I like but is there a formula that can autosum the Total weights i.e
    1lb 14oz 1lb 1oz 1lb 5oz which using autosum gives 3lbs 20oz and what i need to do then is convert the 20oz to read 1lb 4oz and add to the total of the lbs which would give me
    4lb 4oz if you get what I am trying to achieve or is it easier to autosum column B and C and do the calculation against the total Row to combine all data as a total

    Last edited by Jacko1307; Jul 21st, 2019 at 07:44 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,230
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Formula to convert Oz back into autosum and add to left of decimal place

    You could give an example of how your data is on your sheet and what you expect from the result.
    I'm not understanding, in B you have lb or oz. In C you have lb or oz.
    Or what exactly do you have?
    Regards Dante Amor

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,858
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to convert Oz back into autosum and add to left of decimal place

    easier to autosum column B and C and do the calculation against the total Row to combine all data as a total
    Seems like a reasonable way to do it. Keep it simple. Why overthink the issue.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,432
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Formula to convert Oz back into autosum and add to left of decimal place

    Maybe something like

    JK
    23.153.9375
    31.141.875
    41.011.0625
    56.14

    Sheet1



    Worksheet Formulas
    CellFormula
    K2=DOLLARDE(J2,16)
    K5=DOLLARFR(SUM(K2:K4),16)

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •