Forum Index » Gear Lists » Gear List Spreadsheet Question

 Display Avatars Sort By: Date (Chronological) Date (Reverse Chronological)
 Sam Haraldson (sharalds) - MLife Locale: Gallatin Range Gear List Spreadsheet Question on 12/27/2006 12:33:16 MST Fellow Statisticians - Can someone please shed some light for me on how to write a formula into my gear spreadsheet that will take my decimal weight and divide it out into a Pounds and Ounces weight (or Kg and g). i.e.40.00 oz.2 lbs. 8 oz.
 paul johnson (pj) - F Locale: LazyBoy in my Den - miss the forest Re: Gear List Spreadsheet Question on 12/27/2006 13:07:39 MST 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((C1-INT(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((C1-INT(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.
 Ben 2 World (ben2world) - MLife Locale: So Cal Re: Gear List Spreadsheet Question on 12/27/2006 15:09:35 MST PJ:Thanks -- I learn something new everyday! However, I believe your first formula should be shown as:=IF(C1>0,INT(C1/16)&"lb, "&ROUND((C1/16-INT(C1/16))*16,1)&"oz",0) Edited by ben2world on 12/27/2006 15:10:13 MST.
 Benjamin Tomsky (btomsky) - F Locale: San Francisco Bay Area Re: Re: Gear List Spreadsheet Question on 12/27/2006 15:21:18 MST I use a similar formula, except add an IF statement to check if the value is greater than 16 oz first, so that if it is not, "0lb" is not displayed. The above formulas will display "0lb, 3oz" but the below will display "3.0oz":=IF(C1>=16,(FIXED(FLOOR(C1/16, 1),0, TRUE) & "lb, " & FIXED(MOD(C1,16), 1, TRUE) & " oz"), FIXED(C1, 1, TRUE) & "oz")
 Ben 2 World (ben2world) - MLife Locale: So Cal Re: Gear List Spreadsheet Question on 12/27/2006 15:23:11 MST The Ben's have it! :)