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

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

 

[David Biddulph]

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

 

[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

 

[bwieg92]

How would you make the first formula hide the 14? When you use that form, if you would type in 12 Stones 6 Pounds it would display 6/14. How would you make it show only the 6?

 

[ninemins]

I can do maths on the deciaml number of pounds but to display stone ponds ounces
I have used a very crude hack

There must be a more refined way to achieve my end result

What I need is take the number of pounds divide by 14 to give nuber of stone
then subtract from the total number of pounds the stone * 14 figure

I then have a number of number of pounds so 162.4
becomes 11 st 8 lbs 6 oz

=TEXT(INT(B2/14),"#")&" st "&TEXT(MOD(B2, 14),"#")& " lbs " &TEXT(MOD(S2, 16),"#")& " oz"

my formula a copy of that posted by Bernard Liengmehas used requires a figure for the number of oz
to be calculated to be used as S2 in the formula.

I guess it is possible to do a MOD of a MOD ie 162.4 pounds divided by 14 gives the number of
stones 11 remainer 0.6 stone = 8.4 pounds = 8 pounds 0.4 * 16 = 6.4 = 6 ounces

I dont even know if traditionally in the UK a fraction of ounces would ever be used.
If so how would I modify the TEXT(MOD(S2, 16),"#") which takes 6.4 oz and rounds to 6

So for mathematical completness it would be 11 st 8 lbs 6.4 oz admittedly more accurate than the scales could measure!

Any help gratefully received

Chris

 

[Mike454]

I can do maths on the deciaml number of pounds but to display stone ponds ounces
I have used a very crude hack

There must be a more refined way to achieve my end result

What I need is take the number of pounds divide by 14 to give nuber of stone
then subtract from the total number of pounds the stone * 14 figure

I then have a number of number of pounds so 162.4
becomes 11 st 8 lbs 6 oz

=TEXT(INT(B2/14),"#")&" st "&TEXT(MOD(B2, 14),"#")& " lbs " &TEXT(MOD(S2, 16),"#")& " oz"

my formula a copy of that posted by Bernard Liengmehas used requires a figure for the number of oz
to be calculated to be used as S2 in the formula.

I guess it is possible to do a MOD of a MOD ie 162.4 pounds divided by 14 gives the number of
stones 11 remainer 0.6 stone = 8.4 pounds = 8 pounds 0.4 * 16 = 6.4 = 6 ounces

I dont even know if traditionally in the UK a fraction of ounces would ever be used.
If so how would I modify the TEXT(MOD(S2, 16),"#") which takes 6.4 oz and rounds to 6

So for mathematical completness it would be 11 st 8 lbs 6.4 oz admittedly more accurate than the scales could measure!

Any help gratefully received

Chris

I had a go myself and came up with this, it may be a little clumsy but is seems to work and give stones, pounds & ounces based on the value of cell A1 (Kilogram weight)

=QUOTIENT(A1,6.350288) & "st " & (QUOTIENT(MOD(A1,6.350288),0.453592)) & "lbs " & QUOTIENT(MOD(A1,0.453592),0.02835) &"ozs"

It's best if you paste it into the formula bar, but if pasted into a cell ensure it's all on one line not two as shown above.
Mike