Custom Number Format for Weight

[Guest]

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,

 

[Earl Kiosterud]

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.

 

[Guest]

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

 

[Guest]

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.