Converting weight

[Mark Allison]

Hi there,

I have a spreadsheet and I want to have four columns:
A - kilogram
B - stone
C - pounds
D - total pounds

I want to be able to enter data in any of the columns (B,C count as one
because someone could weigh 11stone 4lb for example) and have the other
columns automatically update.

I've managed to do this if someone enters their weight in stone and
pounds, but I have no idea how it would be achieved if I entered data in
any one of the columns.

Ideas? Can I use some kind of IF statement?

Thanks

 

[Pete_UK]

You will have to enter the data into a different column and also
indicate the units you are entering, as you can't have both a formula
and a value in a cell.

Hope this helps.

Pete

 

[Roger Govier]

Hi Mark
Assuming that there will either be an input in column A, or B &C and
that you have your headers in row 1,
then in D2 enter
=IF(A2>0,CONVERT(A2*1000,"g","lbm"),B2*14+C2)

If you also wanted a Kilo entry to not only show total pounds, but Stone
and pounds, or stone and pound entries to be shown as kilos, then mirror
your headings in columns E to G
E - Stone
F - Pounds
G - Kilos
In cell E2 enter
=IF(A2>0,INT(D2/14),"")
in F2 enter
=IF(A2>0,MOD(D2,14),"")
in G2 enter
=IF(A2=0,D2/2.205,"")

Format cells to required number of decimal places and copy down as far
as required.