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

  • Thread starter Thread starter paula23234
  • Start date Start date
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?
 
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
 
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?
 
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
 
ninemins said:
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
Click to expand...



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
 
Last edited:
Back
Top