Solved

Excel 2000:  Convert ounces to pounds and ounces

Posted on 2002-04-03
5
4,125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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