Can you convert 120lbs to show as 8st 8lbs?

[carol]

Does excell recognise weight in st and lbs? Iam trying to devise a weight
monitoring s/s that tracks the percentage gain or loss each week, but want it
to be shown in st and lbs not total lbs.

Thanks

Carol

 

[Mike H]

Hi,

With pounds in A1 try this

=INT(SUM(A1)/14)&" Stones "&MOD(A1,14)&" Pounds "

Mike

 

[Mike H]

Where did SUM creep in, try this instead

=INT(A1/14)&" Stones "&MOD(A1,14)&" Pounds "

Mike

 

[carol]

Hi Mike,

Thank very much - that worked.

I have another problem to solve - perhaps you could help.

In column A, i have put the starting weight of everyone in the group. In
column B, I want to put the latest weight of the group. I therefore need a
formula that will look through the data range, pick out the latest date and
then populate the column with the corresponding weight from that date. Any
ideas?

Thanks

Carol

 

[Mike H]

Carol,

To do this I assume you mean by person. Post a small sample of data shoing
your column layout.

Mike

 

[Sean Timmons]

Are all individuals weighed on the same date?

=SUMPRODUCT(--(Sheet2!$A$2:$A$500=A1),--(Sheet2!$B$2:$B$500=MAX(Sheet2!$B$2:$B$500)),Sheet2!$C$2:$C$500)

Assumes:
A1 has your current sheet's trainee name
Sheet2 has your weigh-ins
Column a of Sheet2 has list of trainee names
Column B of Sheet2 has dates of weigh ins
Column C of Sheet2 has weights
There are less than 500 total rows in sheet2.

 

[carol]

Ok - see below.

I have two sets of data recording the weight. One is entered in total lbs
i.e. 120.5 lbs. From this data range, i have created another, showing the
weight in stones and lbs using the fromula you gave me - this is easier for
everyone to understand and track their progress.

From this second data range, I want to pick out the latest weight so that it
can be compared to the starting weight of each member in the group in a
summary part of the spreadsheet. This summary will consist of Column A
(names), Column B (Starting Weight) Column C (Latest Weight) COlumn D
(Percentage change between B & C). (Column D will be calculated from the 1st
data range, i.e. total lbs not stones and lbs).

However, i want Column C to be derived from the date range that shows the
weight in stones and lbs, and therfore looks throughout cells B18 to P26 and
return the entries below the latest date in the range. Below is an example of
how the data is laid out (1st version - in lbs):

July September
31/07/09 18/09/09 25/09/09
Andrew 142.75 - 140.75
Carol 130.5 131.5 126.75
Ewan 184.25 - 184.25
Joy 117.5 117.5 119.5
Jamesina 140.25 141.25 142
Kerry 150.5 - 134.75
Laura 150.5 151 151.25
Mark 201.5 205.25 202.75