P

#### paula23234

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?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

P

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?

D

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

B

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

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 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: