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.

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.

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.

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")

The Ben's have it! :)

my formula came from a working gearlist spreadsheet that i've had for a couple of yrs now.

i can understand how this misunderstanding came about. reread my initial post. i put 9.39 on a line all by itself - this led to the misunderstanding of what value i was attempting to convert. the next line of text indicates that its units was pounds. had i written 9.39lb, all would have been clearer and probably no misunderstanding would have occurred. sorry, i was not clearer.

the use c1/16 in both suggested corrections is not correct for my formula. it will not produce the correct answer. to test my intuition on this point, i simply copy and pasted the suggested formulas into blank cells of my gearlist spreadsheet. in both cases 9.39 lbs was converted to 0lb, 9.4oz - not a good answer to say the least.

using just C1 is sufficient by itself. dividing the number of pounds by 16 will NOT give pounds.

hence, using my previous example, the suggested formulas using c1/16 will yield an answer of "0lb, 9.4oz". Clearly, this cannot be the equivalent of 9.39lb.

simple way to prove this is, using a totally blank spreadsheet, place 9.39 in cell C1 and then copy and paste each formula (mine and the suggested correction) into other cells in the spreadhseet. Examine the results. Which one, the original or the suggested correction produces the correct approx. answer of "9lb, 6.2oz"?

however, the attempt to display just oz w/o a lb figure when it is <16oz is a good suggested enhancement, IMHO.

Edited by pj on 12/27/2006 15:54:21 MST.

The issue is that my formula takes ounces as input and gives "#lb, #.#oz" as output, while PJ's formula takes decimal lb as input and gives "#lb, #.#oz" as output. They are both useful, for different purposes....

Edited by btomsky on 12/27/2006 15:45:24 MST.

right you are. oz's to lb-oz is a good thing too.

Thanks, PJ and "Bens" for the insightful look into my dilemna. I added the formula into my spreadsheet and it works great. I didn't use Ben's "less than 16" addition because I'm just using it on my "from skin out" weight which (I dont' think) will ever get below 1 lb.

Obviously the Bens and PJ are smarter than me, using a spreadsheet. I would however propose sth a bit simpler so people new to spreadsheet programming can understand what happens:

Firstly measure the weight of all your individual items in oz, (in collum C rows 1 to 30).

At the end summarize all weights in oz, so enter in cell C31:

=SUM(C1:C30)

this will give you total weight in oz

Next in cell C32 calculate from oz to lbs by deviding by 16:

=C31/16

Than in cell C33 enter:

=ROUND(C55;0)

this will give the amount of pounds, than in cell C34:

=(C55-ROUND(C55;0))*16

will give you the amount of ounces.

I know this is way less ellegant, but it's also better understandable for novice spreadsheet users.

Eins

Note that i'm using open office instead of excell and i'm using the programm in dutch instead of english, so i'm noet 100% sure if the word 'ROUND' will work in your spreadsheet

Good suggestion. KISS (keep it short and simple). I like it.

By putting this formula:

=ROUND(C31;0)+((C31-ROUND(C31;0))*1,6)

in cell C32, will give you the number of pounds befor the decimal point and the number of ounces behind the decimal point. In my own spreadsheet my total packweight in decimal pounds is 4.24lbs. The formula will give 4.39; meaning that my total packweight is 4 lbs 3.9 oz.

If it's confussing to realize that 4.39 equals 4 lbs 3.9 oz, one might choose in the cell options to round the number to 1 number behind decimal point, giving: 4.4, so 4 lbs 4 oz.

Eins