Solved

Excel 2000:  Convert ounces to pounds and ounces

Posted on 2002-04-03
5
4,035 Views
Last Modified: 2009-09-13
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.
0
Comment
Question by:cccgsmith
  • 2
  • 2
5 Comments
 

Accepted Solution

by:
tsolen earned 100 total points
ID: 6916717
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6920299
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
 
LVL 1

Author Comment

by:cccgsmith
ID: 6920974
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6921023
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
 
LVL 1

Author Comment

by:cccgsmith
ID: 6921053
That is what I thought I was doing.  But, I will try it again.  Thanks.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question