Formula needed

We are trying to workout a formula to do with weight loss. So far we have
everything correct apart from one colum and thats column E

Column B = Weight in Kgs so B4 = 151.5 (formula =Sheet1!$C$25 - which is
151.5 (weight in Kgs)
Column C = BMI so C4 = 45.24 (formula =Sheet1!$H$25 - which is 45.23874
(Body Mass Index BMI)
Column D = Weight loss in Kgs (calculation we use is Column B figure minus
Column C figure)
Column E = Weight in Stones & Pounds ( This is where we are struggling to
come up with a formular that works)
Column F = Loss in pounds (And as a consequence of column E we are having
problems with column F

Can anyone give us a clue as to what we can use in the way of formulas for
column E and F.

TIA

[Bernard Liengme]

You have the cart before the horse!
It is easy to convert kg to lb with either =CONVERT(C4*1000,"g","lbm") or
=C4*2.2046 in F4
To get stones & lbs, you could use in E4: =INT(F4/14)&"st
"&ROUND(MOD(F4,14),0)&"lbs"
or in E4 use =F4/14 and use custom format # ??/14
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"<<S>>" <[email protected]> wrote in message
news:%[email protected]...
> We are trying to workout a formula to do with weight loss. So far we have
> everything correct apart from one colum and thats column E
>
> Column B = Weight in Kgs so B4 = 151.5 (formula =Sheet1!$C$25 - which is
> 151.5 (weight in Kgs)
> Column C = BMI so C4 = 45.24 (formula =Sheet1!$H$25 - which is 45.23874
> (Body Mass Index BMI)
> Column D = Weight loss in Kgs (calculation we use is Column B figure minus
> Column C figure)
> Column E = Weight in Stones & Pounds ( This is where we are struggling to
> come up with a formular that works)
> Column F = Loss in pounds (And as a consequence of column E we are having
> problems with column F
>
> Can anyone give us a clue as to what we can use in the way of formulas for
> column E and F.
>
> TIA
>

Thanks for that, one small problem though, in the E column, where it gives
the conversion into Stones and pounds, it gives it as a percentage of a
Stone, we want it to read say 23 Stone 12 pounds, how can we do it?


"Bernard Liengme" <[email protected]> wrote in message
news:%[email protected]...
> You have the cart before the horse!
> It is easy to convert kg to lb with either =CONVERT(C4*1000,"g","lbm") or
> =C4*2.2046 in F4
> To get stones & lbs, you could use in E4: =INT(F4/14)&"st
> "&ROUND(MOD(F4,14),0)&"lbs"
> or in E4 use =F4/14 and use custom format # ??/14
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "<<S>>" <[email protected]> wrote in message
> news:%[email protected]...
>> We are trying to workout a formula to do with weight loss. So far we
>> have everything correct apart from one colum and thats column E
>>
>> Column B = Weight in Kgs so B4 = 151.5 (formula =Sheet1!$C$25 - which is
>> 151.5 (weight in Kgs)
>> Column C = BMI so C4 = 45.24 (formula =Sheet1!$H$25 - which is 45.23874
>> (Body Mass Index BMI)
>> Column D = Weight loss in Kgs (calculation we use is Column B figure
>> minus Column C figure)
>> Column E = Weight in Stones & Pounds ( This is where we are struggling to
>> come up with a formular that works)
>> Column F = Loss in pounds (And as a consequence of column E we are having
>> problems with column F
>>
>> Can anyone give us a clue as to what we can use in the way of formulas
>> for column E and F.
>>
>> TIA
>>
>
>