+ Reply to Thread
Results 1 to 5 of 5

Custom Number Format for Weight

  1. #1
    Tom
    Guest

    Custom Number Format for Weight

    I have a formula that I would like to display the result as pounds and
    ounces. I created and custom format that will display as (for example) "14.25
    lbs" but I would like it to display as 14 lbs 4 oz. It must display as a
    number and not text. Any help would be great!

    Thanks,

  2. #2
    Earl Kiosterud
    Guest

    Re: Custom Number Format for Weight

    Tom,

    No can do with a format, methinks. How about displaying pounds and ounces
    in another cell (column)?
    =INT(A2) & " lbs " & MOD(A2,1)*16 & " oz"

    This yields a text string, so you can't do any math on it; use the original
    (A2) cell for that.
    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "Tom" <[email protected]> wrote in message
    news:[email protected]...
    >I have a formula that I would like to display the result as pounds and
    > ounces. I created and custom format that will display as (for example)
    > "14.25
    > lbs" but I would like it to display as 14 lbs 4 oz. It must display as a
    > number and not text. Any help would be great!
    >
    > Thanks,




  3. #3
    Tom
    Guest

    Re: Custom Number Format for Weight

    Thanks Earl,

    I tried what you suggested and it still does not display correctly my number
    is in cell C20 and now reads 7. 13 lbs I entered your suggestion into C24 and
    changed the "A2" to "C20" and the result is 7 lbs 2.07812499.

    I think I have seen this Custom format before but I can't seem to find it
    now. My current cell C20 is a formula (=$H$5/12*$J$5) I am converting board
    feet to cubic feet and multiplying the cubic feet by the pounds per cubic
    feet (J5). I can deal with the fraction but I want to send this sheet out so
    several factory employees can use it to check a raw material and see if we
    are getting what we pay for.

    Thanks again,

    Tom

    "Earl Kiosterud" wrote:

    > Tom,
    >
    > No can do with a format, methinks. How about displaying pounds and ounces
    > in another cell (column)?
    > =INT(A2) & " lbs " & MOD(A2,1)*16 & " oz"
    >
    > This yields a text string, so you can't do any math on it; use the original
    > (A2) cell for that.
    > --
    > Earl Kiosterud
    > mvpearl omitthisword at verizon period net
    > -------------------------------------------
    >
    > "Tom" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have a formula that I would like to display the result as pounds and
    > > ounces. I created and custom format that will display as (for example)
    > > "14.25
    > > lbs" but I would like it to display as 14 lbs 4 oz. It must display as a
    > > number and not text. Any help would be great!
    > >
    > > Thanks,

    >
    >
    >


  4. #4
    IanRoy
    Guest

    Re: Custom Number Format for Weight

    Hi, Tom;
    " 7 lbs. 2.07812499 ozs. appears to be correct if your original number (the
    result of your formula: =$H$5/12*$J$5) is 7.129882811875. To round off your
    display, modify Earl's formula as follows (using C20):
    =INT(C20) & " lbs " & ROUND(MOD(C20,1)*16,0) & " oz"
    This will show the result: 7 lbs 2 oz. I don't know how to do that using a
    format either.
    Regards,
    Ian.

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

    A compromise might be to format the number as (custom format) # " lb" ??/16.

    This would give results like 7 lb 2/16. Maybe your colleagues could interpret 2/16 as 2 oz?

    This would still leave a number in the cell, not text.

    Alf

+ 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