Convert Ounces to Pounds & Ounces

[gumuruku]

I'd like to convert ounces (e.g., 33 oz) to pounds and ounces (e.g. 2 lbs, 1 oz). Any suggestions?

 

[Oaktree]

Welcome to the board.

Try:

=INT(A1/16)&" lbs "&(A1-INT(A1/16)*16)&" oz"

Where A1=33.

 

[gumuruku]

This works great! Thanx!

 

[gumuruku]

Followup question

I have a followup question.

If I enter a value like 16.3 lbs, the formula above returns 1 lb 0.300000000000001 oz. The correct answer should be 1 lb 4.8 oz. I've tried various ROUND functions but can't get this to work. How can I modify the syntax provided above to correct for this?

 

[gumuruku]

Correction to my followup question

I meant to say that if I enter 16.3 oz, I get a value = 1 lb 0.300000000000001 oz. It should be 1 lb 4.8 oz.

 

[daniels012]

Actually the result for 16.3oz. is 1.01875

There are add ins for excel that has this. Andrews website is a great download. Use this, it has a conversion tool. Go to Data Utilities then number 13.

http://www.andrewsexceltips.com/my_utilities.htm

Hope this helps,
Michael

 

[gumuruku]

STOP - I messed up! One more time!
What I REALLY want to do is this:
If I enter 16.3 oz, I'd like to obtain a result rounded to the nearest tenth. So, I want to convert 16.3 oz and obtain a result = 1 lb 0.3 oz.

 

[Greg Truby]

Just slip a TEXT() into Oaktree's formula, setting the 2nd arg of TEXT() per your requirements.<ul>[*]=INT(A1/16)&" lbs "&TEXT((A1-INT(A1/16)*16),"0.0 oz")[/list]{edited to put the OZ inside the TEXT() function. geez.}

 

[Oaktree]

Try changing

&(A1-INT(A1/16)*16)&

to

&round(A1-INT(A1/16)*16,1)&

 

[Greg Truby]

Just for reference, you might also take a look at the <ul>[*]DollarDe() and[*]DollarFr()[/list]functions in Excel help. They can be very useful in converting things like pounds & ounces, feet & inches and so forth.