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

P

paula23234

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?
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to format in stones & pounds and then present on chart 1
A few problems involving LOOKUP and SUMPRODUCT... 2
How do I format cells to accept stones and pounds? 3
how can i use imperial units eg stones and pounds 2
How can I add pounds and ounces in excel, 3
Stones,Pounds and Ounces 4
how do i use excel to calculate weight (as in stone and lbs)? 6
I don't think ANYONE can add pounds and ounces! 9

Top