Have a cell display decimals as lbs & oz

Hi,

I am trying to find a way to format cells to show pounds and oz without using formulae. e.g. I want to be able to type in 151.25 and have it display as 151lbs 4oz. I currently have it showing 151 1/4lbs but I was just curious to know if it could be formatted to automatically show 4oz instead of 1/4lb. I know this is petty but then that's OCD for you!!

Steve

A custom number format of

0"lbs" ?/16"oz"
gets close but includes an unwanted "/16"

I can't think of anything better without using formulas

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Excel has the basics of that format as a built-in fraction format


but, as Bill says, the "/16" will always show. So, just formatting the value cell won't get the desired result. 

You can use a formula to show the desired representation in a different cell:

=INT(A1)&" lbs "&MOD(A1,1)*16&" oz"

cheers, teylyn
___________________
cheers, teylyn
 
Community Moderator
www.teylyn.com

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated January 24, 2024 Views 742 Applies to: