Solved

Excel 2000:  Convert ounces to pounds and ounces

Posted on 2002-04-03
5
3,968 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now