How do I show Stones and Pounds

Posted by Liam Redman on July 09, 2001 7:27 AM

I have created a simple table for people to enter their weight in Kilograms in column A. I have a formula in Column B which converts this weight to Pounds (=A1*2.2). In Column C I want to show the weight in Stones and Pounds. E.G. i have a formula in column C (=B1/14) This shows the weight as say for example 10.5 stones instead of 10 stones 7 pounds. How do I make column C have a unit of 14 instead of 10. Maybe this is not possible! Any help would be appreciated. Thanks

Posted by Ben O. on July 09, 2001 7:40 AM

Try this formula. I tested it on your sample values and it works:

=TRUNC(B1/14) & " stones " & SUM(B1/14,-TRUNC(B1/14))*14 & " pounds"

-Ben

Posted by Ben O. on July 09, 2001 7:47 AM

One more thing...

I tried this formula on some more values and noticed for some it gives the pounds with many decimal places. This revised formula will round the pounds to the nearest integer, if that's what you want:

=TRUNC(B1/14) & " stones " & ROUND(SUM(B1/14,-TRUNC(B1/14))*14,0) & " pounds"

-Ben

Posted by Liam Redman on July 10, 2001 4:58 AM

Re: One more thing...

Many thanks Ben O for your help, it works a treat.
Liam