Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: LBS OZ formula

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default LBS OZ formula

    Hi, I have done a lot of searching everywhere and came up with a few different ways of doing this, but the final formula that started working for me needs help. Here is what I need:

    I can only export the data i need from a price field ie: 0.00 due to the limitations of the program I use. A lot of the weight i need to calculate is less than 1oz.

    So I have entered in the price field 112.3 this comes out to 1lb 12.3oz next line entered at 3.7 (3.7oz) this should come out to 2lbs total but i the formula is producing 2lbz 16oz until i add another oz then the 16oz goes away correctly.

    Because I am very rusty with formula's i have only been able to create my weights in A1:A300 then in B1 i have =sum(A1:A300)/100 producing (1.16)
    then in C1 i have:

    =INT(SUM(INT(B1))+SUM(MOD(B1,1)*100)/16)+MOD(SUM(MOD(B1,1)*100),16)/100

    hope this makes sense if not just yell at me.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: LBS OZ formula

    A
    B
    C
    1
    data
    lbs
    2
    112.3
    1.769
    B2: =DOLLARDE(A2/100, 16)
    3
    3.7
    0.231
    4
    2.000
    B4: =SUM(B2:B3)

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LBS OZ formula

    Thanks for the reply, i was trying to get away from copying a formula down (Not a big deal) but the sum does not go down to the 10th's i need to full weight.

    112.3 1.76875
    3.7 0.23125
    0.1 0.00625
    1.5 0.09375
    2.1

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: LBS OZ formula

    Maybe
    ABC
    1112.31.768752.1
    23.70.23125
    30.10.00625
    41.50.09375
    52.1

    Sheet1



    Array Formulas
    CellFormula
    C1{=SUM(DOLLARDE(A1:A4/100,16))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

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

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LBS OZ formula

    Sorry forgot to explain the reply. I was getting 2.1 instead of 2.01.6 like my formula generates. and this happens when adding more:

    112.3 2.2125
    3.7
    0.1
    1.5
    0.9
    0.9

    The correct results i need would be 2.03.4 (2.LBs 03.4Oz) but its giving 2.2125

    Thanks again.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: LBS OZ formula

    In that case how about
    =DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
    Also array entered
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LBS OZ formula

    Quote Originally Posted by Fluff View Post
    In that case how about
    =DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
    Also array entered
    It looks like its working perfect. Thank you very much!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: LBS OZ formula

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LBS OZ formula

    Not sure what happened but see if this makes sense. In real life application I had a total in column A of 51.95 (5195)/100 the formula is showing 62lbs 03.0Oz

    Actual 62.1875 with the other formula you showed me =SUM(DOLLARDE(A1:A400/100,16))

    Shouldn't it be showing 63lbs 02.7oz?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: LBS OZ formula

    If your total is 51lbs 95oz I make that 56lbs 15oz
    - 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
  •