Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2000:  Convert ounces to pounds and ounces

Posted on 2002-04-03
5
Medium Priority
?
4,501 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 400 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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

571 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