How can I sum Pounds & Ounces with Excel

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

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.

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

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

If you want a worksheet function, this should work:

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

Chris

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

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

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

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

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!