Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

How can I sum Pounds & Ounces with Excel

Posted by Steve on September 03, 1999 9:28 PM
I need to add pounds and ounces and have Excel show the totals. I need the totals to reflect the actual pounds and ounces. For example: When I add 10 pounds and 12 ounces to 10 pounds and 12 ounces. I want the total to be 20 pounds 8 ounces. I have tried to divide the ounces by 16 and add it to the pounds. But I get 20.5 pounds as the total instead of 20.8 pounds which would be the correct weight. If anyone could help I sure would appreciate it a bunch!!!

Check out our Excel Resources

Re: How can I sum Pounds & Ounces with Excel

Posted by Ivan Moala on September 04, 1999 3:03 AM

Steve,
You say that you have tried dividing the ounces
by 16 but get 20.5 (I thick the correct result
is 21.5 or 21 lbs 8 ozs) if you take this a few steps
further you should multiply the .5 by 16 to get 8,
then concactenate the results to give you what you
want eg. if you have the lb result in A1 (i'm assuming that you broke the lbs and ozs up?) and
the .5*16 =8 in B1 then Result = A1 & "." & B1 = 21.8 (as a text!!).
However I don't know what result you want or how you what you want to do with the result, so this may not suit?? in which case you may need a UDF
User Defined Function.


Ivan


Re: How can I sum Pounds & Ounces with Excel

Posted by Steve on September 04, 1999 11:47 AM

Thanks for the reply Ivan. It was late when I posted my question. So let me explain what I have tried agian. In column (A) I enter the pounds and in column (B) the ounces. Here is my example: In A1 and A2 I have entered 10. In B1 and B2 I have entered 12. I use the formula =sum(A1:A2) I get 20 in A3. I use the formula =sum(B1:B2)/16 and get 1.5 in cell B3. If I sum A3:B3 I get 21.5 and would prefer 21.8 as the answer. You mentioned that if I take it a few steps
further and multiply the .5 by 16 I would get 8! How do I multiply just the (.5) of the 1.5? Is there something that I can add to the formula =sum(B1:B2)/16 to get the 1.8? If so I could then sum A3:B3 and get 21.8

What I want to end up with is a sheet that I can add weight to and have it keep a running total of the Pounds and Ounces as I add more weight. Someone might have a better method instead of separating the pounds from the ounces. I just thought that it might be easier, if I did seperate them in different columns.


Re: How can I sum Pounds & Ounces with Excel

Posted by Ivan Moala on September 04, 1999 11:07 PM

Steve,
Try this UDF (User Defined Function), soemone might come up with a better one.

Function Pd(ByVal PoundRange As Range, ByVal OzRange As Range) As String
Dim SumPounds As Integer
Dim IntSumOz As Integer

IntSumOz = Int((Application.WorksheetFunction.Sum(OzRange)) / 16)
SumPounds = Application.WorksheetFunction.Sum(PoundRange)
Pd = SumPounds + IntSumOz & "." & ((Application.WorksheetFunction.Sum(OzRange) / 16) _
- IntSumOz) * 16

End Function

Copy & Paste into a module
To use it type in;

Pd("your range of pound values", "your range of oz values")

Use it as a normal function ie select the range of values for the pounds,
& the range of values for the oz.
Note: the result is a text string. To convert it back use something like
Value(<TextString>,2)

This should give you the correct result over a user selected range.

regards


Ivan



Re: How can I sum Pounds & Ounces with Excel

Posted by Ivan Moala on September 05, 1999 1:51 AM
Steve
Made an error, was thinking in terms of a string
change it to;


Function Pd(ByVal PoundRange As Range, ByVal OzRange As Range) As Double
Dim SumPounds As Integer
Dim IntSumOz As Integer

IntSumOz = Int((Application.WorksheetFunction.Sum(OzRange)) / 16)
SumPounds = Application.WorksheetFunction.Sum(PoundRange)
Pd = SumPounds + IntSumOz & "." & ((Application.WorksheetFunction.Sum(OzRange) / 16) - IntSumOz) * 16
End Function


This should give you a value instead of a string!


Ivan


Re: How can I sum Pounds & Ounces with Excel

Posted by Chris on September 07, 1999 9:43 AM
If you want a worksheet function, this should work:

=SUM(A1:A2)+INT(SUM(B1:B2)/16)+MOD(SUM(B1:B2),16)/10

Chris


Re: How can I sum Pounds & Ounces with Excel

Posted by Chris on September 07, 1999 9:55 AM
Oops! I take that back, it won't work in a case where the onces remaining is greater than 9. I guess I should really test the formulas before I post them.

I'll open Excel and try it next time!

Sorry,
Chris


Re: How can I sum Pounds & Ounces with Excel

Posted by Chris on September 07, 1999 10:02 AM
OK,

I thought my logic was sound. It turns out you can just change that /10 at the end to /100. This will differ from Ivan's in that 31lbs and 4oz would show as 31.04. 31lbs and 12oz would be 31.12.

Please pardon my blunders.

Chris


Re: How can I sum Pounds & Ounces with Excel

Posted by Ivan Moala on September 07, 1999 8:47 PM
I thought my logic was sound. It turns out you can just change that /10 at the end to /100. This will differ from Ivan's in that 31lbs and 4oz would show as 31.04. 31lbs and 12oz would be 31.12. Please pardon my blunders.

Chris, you should watch your blunders ;-),
I know what it's like, I've done the same thing
MANY times :-)

Ivan



Re: How can I sum Pounds & Ounces with Excel

Posted by Chris on September 08, 1999 11:03 AM


I'm sorry for my blatent display of incompetence. I have clearly shattered my image of a reasonably well versed Excel user. Not that I had any respect around here to lose. 8^(

Chris


Re: How can I sum Pounds & Ounces with Excel

Posted by Steve on September 18, 1999 3:14 PM

Thanks for the help sorry I took so long to thank you guys. I have been out of town. It seems to work pretty good thanks again!


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.