Assume cell C1 has the following value in it:
9.39
which in this example is the weight in lb of your base pack wt.
=IF(C1>0,INT(C1)&"lb, "&ROUND((C1INT(C1))*16,1)&"oz",0)
the above formula produces an approximate value of
9lb, 6.2oz
[Note: to learn what each part of the above formula is doing, just type parts of it into a cell and see what it produces. this will be more instructive than a lengthy/wordy explanation of what each part of the formula is attempting to accomplish.]
in many cases you could get by w/o the IF test portion of the formula, so, just...
=INT(C1)&"lb, "&ROUND((C1INT(C1))*16,1)&"oz"
will work in most cases. in this example, with the IF, you'll simply get a "0", while w/o it, you'll get "0lb, 0oz"  more consistent output, but perhaps the breakdown into lb & oz is unecessary when we're dealing with zero.
also, adjust the "rounding" if you require more digits of precision. personally, i felt that tenths of an ounce sufficed, but perhaps others would like more precision.
altering the above formula for metric output is fairly straight forward, though, if i'm not mistaken, you will leave it in a decimal form, i.e., you don't want it broken out into XXkg, xxgm output. so, in this case, you really don't want a formula (unless you're converting pounds to kilograms  if so, read on, one suggested solution follows).
to have user selectable dimensional output, just use the version of the formula with the IF and have it reference a cell that indicates whether output should be in "lb, oz" or metric. of course, when metric output is selected by the user, the ELSE consequent of the formula [represented by the ",0)" in the above formula, would read ",round(C1/2.204622622,2))" to get approx. wt. in kg rounded to two decimal digits.]
i'm sure that there are other sol'ns to this problem also.
is this sol'n close enough for your purposes? if it's unclear, or you don't want rounding, etc., just post back with one or more specific questions and i (or others) will try to respond.
Edited by pj on 12/27/2006 14:39:21 MST.
