+ Reply to Thread
Results 1 to 7 of 7

How can I add pounds and ounces in excel,

  1. #1
    Agoogirl
    Guest

    How can I add pounds and ounces in excel,

    I need to keep a running total of weight, in pounds and ounces, for 12
    months. I'd like to add 6.02 (6lbs 2ozs), 9.13 (9lbs 13 ounces)etc and be
    able to total 12 different numbers. How do I get the number to recognize
    16ozs as 1 pound? Is there a better way?

  2. #2
    pinmaster
    Guest
    Hi
    You can use a custom format for your numbers...Format/Cells..select custom and enter the following:

    0"lbs".00"oz"


    Jean-Guy

  3. #3
    pinmaster
    Guest
    Hi
    Sorry but I totally misunderstood your question so please disregard my previous post!

    Jean-Guy

  4. #4
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    Hi!

    Have a look at

    www.excelforum.com/showthread.php?t=227781

    OK, this relates to feet and inches, but change the 12's for 16's etc and all the principles are the same.

    Alf

  5. #5
    Earl Kiosterud
    Guest

    Re: How can I add pounds and ounces in excel,

    Agoo,

    One way is to put the pounds in one column (say A), and the ounces in
    another (say B). Sum them with:

    For the pounds: =INT(SUM(A2:A10) + SUM(B2:B10)/16)
    For the ounces: =MOD(SUM(A2:A10) + SUM(B2:B10)/16,1)*16

    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "Agoogirl" <[email protected]> wrote in message
    news:[email protected]...
    >I need to keep a running total of weight, in pounds and ounces, for 12
    > months. I'd like to add 6.02 (6lbs 2ozs), 9.13 (9lbs 13 ounces)etc and be
    > able to total 12 different numbers. How do I get the number to recognize
    > 16ozs as 1 pound? Is there a better way?




  6. #6
    Dave Peterson
    Guest

    Re: How can I add pounds and ounces in excel,

    I like Earl's suggestion of putting the different units into different fields,
    but if you didn't...

    These are all array formulas. That means you hit ctrl-shift-enter instead of
    enter. If you do it correctly, excel will wrap curly brackets {} around your
    formula. (don't type them yourself.)

    This will add up the whole numbers in your range:
    =SUM(INT(A1:A10))
    This will add up the ounces and find out how many pounds (carry the x stuff):
    =INT(SUM(MOD(A1:A10,1))*100/16)
    This will add up just the ounces portion (no carrying)
    =MOD(SUM(MOD(A1:A10,1))*100/16,1)*16

    So you could use a giant formula like:

    =SUM(INT(A1:A10))+INT(SUM(MOD(A1:A10,1))*100/16)
    +(MOD(SUM(MOD(A1:A10,1))*100/16,1)*16/100)

    (all one cell, and remember to hit ctrl-shift-enter)

    But the bad news is that there can be some rounding errors. This worked better
    for me in my testing:

    Whole numbers:
    =SUM(INT(A1:A10))
    pounds in the ounce total:
    =INT(ROUND(SUM(MOD(A1:A10,1)),3)*100/16)
    ounces in the ounce total:
    =MOD(ROUND(SUM(MOD(A1:A10,1)),3)*100/16,1)*16

    And as a giant formula:
    =SUM(INT(A1:A10))+INT(ROUND(SUM(MOD(A1:A10,1)),3)*100/16)
    +MOD(ROUND(SUM(MOD(A1:A10,1)),3)*100/16,1)*16


    Agoogirl wrote:
    >
    > I need to keep a running total of weight, in pounds and ounces, for 12
    > months. I'd like to add 6.02 (6lbs 2ozs), 9.13 (9lbs 13 ounces)etc and be
    > able to total 12 different numbers. How do I get the number to recognize
    > 16ozs as 1 pound? Is there a better way?


    --

    Dave Peterson

  7. #7
    Sandy Mann
    Guest

    Re: How can I add pounds and ounces in excel,

    Agoogirl,

    As an academic exercise, with the data in G1:G12 in the form that you gave
    (ie 6.02 etc.) then

    =INT(SUM(INT(G1:G12))+SUM(MOD(G1:G12,1)*100)/16)+MOD(SUM(MOD(G1:G12,1)*100),
    16)/100

    entered as an array formula by pressing Control + Shift + Enter intead of
    just enter will give you the answer you seek but I do not recommend it - go
    with Earl's solution.

    Regards

    Sandy

    --
    to e-mail direct replace @mailintor.com with @tiscali.co.uk


    "Agoogirl" <[email protected]> wrote in message
    news:[email protected]...
    > I need to keep a running total of weight, in pounds and ounces, for 12
    > months. I'd like to add 6.02 (6lbs 2ozs), 9.13 (9lbs 13 ounces)etc and be
    > able to total 12 different numbers. How do I get the number to recognize
    > 16ozs as 1 pound? Is there a better way?





+ 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