Cell formatting in excel
 

How do I format to show lb & stones eg 24lb = 1st 10lb

Cell formatting in excel
 

You can't do it by cell formatting.

=INT(A1/14)&"st "&MOD(A1,14)&"lb"
--
David Biddulph

"Odyssey 62" wrote in message
...
How do I format to show lb & stones eg 24lb = 1st 10lb

Cell formatting in excel
 

Hi,

I'm not sure about a format but with a number of pounds in A1 try this.

=INT(A1*0.0714286)&" St " &INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&"
lbs"

It will start losing accuracy at around 2500000 pounds.

Mike


"Odyssey 62" wrote:

How do I format to show lb & stones eg 24lb = 1st 10lb

Cell formatting in excel
 

Dividing by 14 is better than muliplying by a rough approximation to the
reciprocal.
And for the remainder after the division, MOD(A1,14) is easier than
INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)
--
David Biddulph

"Mike H" wrote in message
...
Hi,

I'm not sure about a format but with a number of pounds in A1 try this.

=INT(A1*0.0714286)&" St "
&INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&"
lbs"

It will start losing accuracy at around 2500000 pounds.

Mike


"Odyssey 62" wrote:

How do I format to show lb & stones eg 24lb = 1st 10lb

Cell formatting in excel
 

David,

When I saw your answer i couldn't believe I'd made such a simple thing so
complicated. Thanks.

Mike

"David Biddulph" wrote:

Dividing by 14 is better than muliplying by a rough approximation to the
reciprocal.
And for the remainder after the division, MOD(A1,14) is easier than
INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)
--
David Biddulph

"Mike H" wrote in message
...
Hi,

I'm not sure about a format but with a number of pounds in A1 try this.

=INT(A1*0.0714286)&" St "
&INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&"
lbs"

It will start losing accuracy at around 2500000 pounds.

Mike


"Odyssey 62" wrote:

How do I format to show lb & stones eg 24lb = 1st 10lb