First understand, I don't really understand formulas, but I can copy into my XL 2007 sheet.
I have a weekly weight in kilo and want a onversion that will show Stone & pounds (without the all the decimal places). What I need is 16 stone 3 pounds NOT 16 stone .00000004 pounds. Many thanks Pete
You can rightclick the field and choose format cell.
Choose digit and set the decimal places.
If you need to round a digit, you have to use:
=round(digit;places)
There may be a more elegant solution but this is the way I'd do it: Once you've converted kilos to stone, say you have your 16.004 in cell A3, then in cell A4 put the formula '=A3-INT(A3)' - this will put the 0.004 into that cell, then in your pounds cell you type the formula '=14*A4' - this will give you the number of pounds - you can do the same again to get ounces or just format the cell to zero decimal places to round it appropriately. For the number of stones, in your 'stones' cell put '=INT(A3)' Hope this makes sense.
"I've always been mad, I know I've been mad, like the most of us..."
Many thanks to all who offered help.
I found the following and it works, again many thanks to all.
=INT(CONVERT(cell,"kg","lbm")/14&" st "&ROUND(MOD(CONVERT(cell,"kg","lbm")/14,1*14,0)&" lb"