Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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;

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!

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.

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.