Excel 2000: Convert ounces to pounds and ounces

I would like to convert ounces to pounds and ounces.  Ideally, I'd like to enter the number of ounces into a cell and have it formatted as pounds and ounces
(Ex. 2lb 12oz).

Any ideas?  Thanks.
LVL 1
cccgsmithAsked:
Who is Participating?
 
tsolenCommented:
Below is a customized function that you can add to your spreadsheet.  Just open the visual basic editor in the excel file and paste this function in a module.  Then you can use this function like any another function in Excel.  Assume that you have the weight in ounces that you want to convert in cell "A1".  Your formula should be "=convertounces(A1)".
P.S. I am not good with ounces and pounds conversion.  I assumed that 1 lb = 16 oz.  If this is not correct please adjust the function.  You just have to change 16 to the correct amount.
-----------------------------
Function convertounces(unit_ounce) As String
Dim pounds As Integer
Dim ounces As Integer
pounds = Int(unit_ounce / 16)
ounces = unit_ounce - (pounds * 16)
convertounces = pounds & "lb." & " " & ounces & "oz."
End Function
-----------------------------
0
 
bruintjeCommented:
Hi tsolen, the custom here is comments instead of locking up questions with a proposed answer

http://www.experts-exchange.com/msoffice/Q.20284589.html#tip

locking up questions takes them away from the main view, and the asker can choose the appropriate comment as answer

cccqsmith > you can also choose for the worksheet functions

for weight conversions

=CONVERT(1, "kg","lbm")

gives 2.204622915 pounds, reverse the order to get kg

=CONVERT(1,"lbm","kg")

0.45359231

=CONVERT(1, "kg","ozm")

gives 35.2739718 ounces

HTH:O)Bruintje

0
 
cccgsmithAuthor Commented:
The custom function will do what I want.  The only problem is that I am having trouble getting it incorporated into the worksheet.  I pasted it into the worksheet using the VB Editor, and then tried using it, but I keep geeting the NAME# error message.  Plus the module that I created seems to be in Book1.xls, not the worksheet that I want to use.  What am I probably doing wrong?

I glad for this experience so I can create future custom functions!

Bruintie, thanks, too for your input.  I appreciate being reminded of the CONVERT function, but I believe that it does not do what I need--converting to both pounds and ounces from ounces.  Thanks, though.
0
 
bruintjeCommented:
the custom function from tsolen has to be in a module in the workbook that you want to use

-so insert a new module there
-paste the code into it
-it will be available

-if you want to make more generic use of custom functions
-create a new module
-insert the functions in there
-choose export module
-name it CustomFunctions.bas
-place it in a central directory like c:\code\mylibs

-now in every future occurence (Word,Excel, etc....)
-in their VB Editor you can choose import module
-and import your lib

HTH:O)Bruintje
0
 
cccgsmithAuthor Commented:
That is what I thought I was doing.  But, I will try it again.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.