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

Advertisements

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
 
Joined
Jul 7, 2009
Messages
1
Reaction score
0
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?
 
Joined
May 7, 2010
Messages
1
Reaction score
0
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
 
Ad

Advertisements

Joined
Aug 27, 2018
Messages
1
Reaction score
1
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
 
Last edited:
Ad

Advertisements


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

Top