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, |
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" wrote in message ... 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, |
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" wrote in message ... 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, |
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. |
All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com