converting grams to pounds and ounces

[thomasm61]

I had no trouble converting pounds and ounces to grams, but it's driving me crazy trying to figure out how to reverse the calculation by grams, so that pounds appear in one cell and ounces in another.

I know I need one calculation/cell for the pounds and another for the ounces, but I'm not sure where to go from there.

 

[MrKowz]

What are the conversion factors for grams>pounds>ounces?

 

[thomasm61]

I was working with the convert command, which provides grams to ounces or pounds; the trick is what to do with the remainder and avoid rounding in the first field. Say your result is 4.6 pounds ... the first field needs to say 4 (w/out rounding up) and the second needs to add the ounces in 16ths of a pound.

I just don't know the syntax to make this happen.

 

[barry houdini]

If you have a number in A1 representing grams then you can use this formula for pounds

=INT(ROUND(A1/28.34952,0)/16)

and for ounces

=MOD(ROUND(A1/28.34952,0),16)

This will round to the nearest ounce

 

[JoeMo]

Here's an alternative using Excel's CONVERT function:

Excel Workbook
	A	B	C
1	grams	lbs	oz
2	470	1	0.58
Sheet12

 

[thomasm61]

Joe: that's the one, thanks.

 

[lenze]

You can also use DOLLARFR

=DOLLARFR(CONVERT(A1,"g","lbm"),16)

Custom format your cell

# "lbs". 00 "oz"

lenze

 

[barry houdini]

=CONVERT(CONVERT($A2,"g","lbm")-INT(CONVERT($A2,"g","lbm")),"lbm","ozm")

Perhaps simpler with

=MOD(CONVERT($A2,"g","lbm"),1)*16

 

[thomasm61]

Thanks for all the tips!

Another question if anybody feels up for a challenge: So I have my three cells -- pounds, ounces, grams -- is it possible to make all the cells convert both ways, so that whenever any number is put a field, its US or metric equivalent shows up in the other?

 

[thomasm61]

Wanted to add that Barry Houdini's formula did exactly what I was trying to figure out how to do.

Also, the same concept applies to converting centimeters/meters to feet and inches; just use "ft" or "in" in place of "lb" and "oz", and "cm" or "m" in place of "g" (and use 12 instead of 16)