how do i format a number into stones, pounds and ounces, please?
 

Hi there, I'm creating a weight tracker but am having problems with the
formulae. I need to format the numbers representing weight in the cells into
stones, lbs and ounces but on't know how. Can anybody advise, please?

how do i format a number into stones, pounds and ounces, please?
 

It's not a question of formatting, but of calculation.
--
David Biddulph

"paula23234" wrote in message
...
Hi there, I'm creating a weight tracker but am having problems with the
formulae. I need to format the numbers representing weight in the cells
into
stones, lbs and ounces but on't know how. Can anybody advise, please?

how do i format a number into stones, pounds and ounces, please?
 

Let's forget the ounces - one sip of water and you weight 2 oz more!

Format the cells with custom format: # ??/14
Then enter the values in this form: 12 6/14 for 12 stone 6 lbs

If you want to enter the value in pounds and display stones & pounds:
In A1, I entered 100, in B1 I have the formula
=TEXT(INT(A1/14),"#")&" st "&TEXT(MOD(A1, 14),"#")& " lbs"
It displays: 7 st 2 lbs
But unlike solution 1, you cannot do any arithmetic with the answer in B1

Time to go metric?
best wishes from Canada
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"paula23234" wrote in message
...
Hi there, I'm creating a weight tracker but am having problems with the
formulae. I need to format the numbers representing weight in the cells
into
stones, lbs and ounces but on't know how. Can anybody advise, please?