I want to create a spreadsheet to track weight loss/gain for a number of
people in our office. The gotcha is that we want to work in Stones and pounds
(1Stone=14Lb). I would like to be able to calculate differences between
cells displayed in Stones/Lbs

Option 1:

Format the cell using the fraction option (2 digits), press ctrl+ 1 to
open the formatting menu, select the number tab, and select the
fraction option.

You can then enter a wight such as "8 10/14".

The downside is that this will be displayed as "8 5/7"

Option 2:
You have to put the Stones and Pounds in seperate columns. say a1 = 8
and b1 = 10 as the start and c1 = 7 and d1 = 13 as the end weight.

The difference is ((c1*14)+d1)-((a1*14)+b1), put this way weight loss
would be negative. Using the above example -11. You could have a cell
to work out the difference using the following:

=IF(((C1*14)+D1)-((A1*14)+B1)<0,"Weight Loss of " &
INT(((((C1*14)+D1)-((A1*14)+B1))*-1)/14) & "St " &
MOD((((C1*14)+D1)-((A1*14)+B1))*-1,14)
&"Lbs",IF(((C1*14)+D1)-((A1*14)+B1)0,"Weight Gain of " &
INT((((C1*14)+D1)-((A1*14)+B1))/14) & "St " &
MOD(((C1*14)+D1)-((A1*14)+B1),14) &"Lbs","No Weight Change"))


HTH

Art


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=345808

"Rod C" wrote:

I want to create a spreadsheet to track weight loss/gain for a number of
people in our office. The gotcha is that we want to work in Stones and pounds
(1Stone=14Lb). I would like to be able to calculate differences between
cells displayed in Stones/Lbs

I have a work around, but its long winded....

First, enter the weight into feilds in text format, using ss-pp, ie 12-04
for twelve stone four pounds (I will assume this is in A1). Then use
LEFT(A1,2) and RIGHT(A1,2) in two cells to the right (B1 and C1) to split
these into two values in seperate cells.

In a D1, do =(B1*14)+C1 , to get the total in pounds. Copy these cells down
all of the rows you plan to use.

Use column E to get the total pounds gained or lost compared to last week.
In the second week, you would enter this into E2: =D2-D1

To convert this pounds back into stone and pounds, put this in F2:
=ROUNDDOWN(E2/14,0) to get the stones gained or lost. In G2: =E2-(F2*14) to
get the pounds gained or lost.

Hope this makes sense...

Tim