Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

RE: How do I format cells to accept stones and pounds?

3 views
Skip to first unread message

Gary''s Student

unread,
Mar 27, 2006, 9:54:02 AM3/27/06
to
In A1 enter:
202

In B1 enter:
=A1*0.0714285781774163


In C1 enter:
=INT(B1)


In D1 enter:
=INT((B1-INT(B1))/0.0714285781774163)


In E1 enter:
=C1 & " st " & D1 & " lb"


and you will see:
202 14.42857279 14 6 14 st 6 lb

You can hide the intermediate columns if you like.
--
Gary''s Student


"Excelnewbie" wrote:

> I am trying to set up a chart showing weight comparisons in stones and pounds.
>
> How do I format cells to accept values e.g. 14st 6lb etc? (I don't just want
> to put in the total pounds value of e.g. 202 instead of 14st 6lb)

David Biddulph

unread,
Mar 27, 2006, 10:52:53 AM3/27/06
to
"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message
news:091B1BFE-BC2E-450E...@microsoft.com...

> "Excelnewbie" wrote:
>
>> I am trying to set up a chart showing weight comparisons in stones and
>> pounds.
>>
>> How do I format cells to accept values e.g. 14st 6lb etc? (I don't just
>> want
>> to put in the total pounds value of e.g. 202 instead of 14st 6lb)

> In A1 enter:


> 202
>
> In B1 enter:
> =A1*0.0714285781774163
>
>
> In C1 enter:
> =INT(B1)
>
>
> In D1 enter:
> =INT((B1-INT(B1))/0.0714285781774163)
>
>
> In E1 enter:
> =C1 & " st " & D1 & " lb"
>
>
> and you will see:
> 202 14.42857279 14 6 14 st 6 lb
>
> You can hide the intermediate columns if you like.

I think the OP wanted it the other way round, & wanted the input to be "14
st 6 lb".

In that case the output in pounds from that input would be something like
=14*(LEFT(A1,FIND("st",A1)-1))+(MID(A1,FIND("st",A1)+2,(FIND("lb",A1)-FIND("st",A1)-2)))but you may need to play around a bit depending on spaces in the inputstring.Again you might decide to produce intermediate columns (hidden if you want)such as=LEFT(A1,FIND("st",A1)-1) for the stones, and=MID(A1,FIND("st",A1)+2,(FIND("lb",A1)-FIND("st",A1)-2)) for the pounds, &then combine them.--David Biddulph

Bernard Liengme

unread,
Mar 27, 2006, 10:59:18 AM3/27/06
to
Alternatively format the cells with custom format # ??/14
Now you can enter, for example, 12 5/14 for 12 stone 5 lbs (I remember that
was my weight when I was 21!)
The advantage being you can do arithmetic on these values.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Excelnewbie" <Excel...@discussions.microsoft.com> wrote in message
news:F93ABD15-EE1A-4B87...@microsoft.com...

0 new messages