Turning Weight (Stone and Punds) into Numbers

[Mark Roberts]

Hi,

I am trying to create a spreadsheet whereby I can turn the weight of someone in to a number.

For instance in A1 I will have 9.12 meaning 9 stone 12 pounds. In Cell B1 I will have 9.09 meaning 9 stone 9 pounds. This is simple as in Cell C1 I can type =A1-B1 and that will tell me how much a person has lost in weight.

My problem lies when I have a weight of say 10 stone and they have now come down to 9 stone 10 pounds. If I put the above formula in it will give me an answer of 90 when I want the answer to be 4. i.e. this person has lost 4lbs in weight.

I would be grateful of any solutions!!

Thanks,
Mark

 

[RoryA]

For instance:
=TRUNC(A1,0)*14+MOD(A1,1)*100-TRUNC(B1,0)*14-MOD(B1,1)*100

 

[MickG]

Bit longer , but should return results as per original data format

=IF((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100)>=14,INT(((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14)+((((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14)-INT(((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14))*14/100,((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100)))

 

[Michael M]

Bit ugly....and the cells needs to be formatted as text

Excel 2007
	A	B	C
1	first weight	finish weight	weight loss
2	9.12	9.09	3.00
3	10.0	9.10	4.00
Sheet1

Cell Formulas
Range	Formula
C2	=LEFT(A2,FIND(".",A2,1)-1)*14+RIGHT(A2,FIND(".",A2,1))-(LEFT(B2,FIND(".",B2,1)-1)*14+RIGHT(B2,FIND(".",B2,1)))
C3	=LEFT(A3,FIND(".",A3,1)-1)*14+MID(A3,FIND(".",A3,1),99)-(LEFT(B3,FIND(".",B3,1)-1)*14+RIGHT(B3,FIND(".",B3,1)))

 

[Mark Roberts]

Thanks a lot Rorya and MickG and Michael M that's great. Does the job exactly how I want.

Much appreciated,
Mark

 

[Mel Smith]

Hello Mark,

I think I've answered your question, although it might be a bit "clunky".

Stones Pounds Stones Pounds Pounds Pounds +/-

9 10 8 13 136 125 -11
10 3 11 2 143 156 13
11 6 11 0 160 154 -6
14 13 14 7 209 203 -6

The first four columns show the relative stones and pounds on patient 1, reading 1 and reading 2. The formula in column 5 is: =INT(A3)*14+B3 and the formula on column 6 is =INT(C3)*14+D3. Column 7 is column 6 minus column 5.

I'm sure that there are slicker way of doing it but I think (hope) it gives you something of what you're looking for.

Mel