How can I add pounds and ounces in excel,

[Guest]

I need to keep a running total of weight, in pounds and ounces, for 12
months. I'd like to add 6.02 (6lbs 2ozs), 9.13 (9lbs 13 ounces)etc and be
able to total 12 different numbers. How do I get the number to recognize
16ozs as 1 pound? Is there a better way?

 

[Earl Kiosterud]

Agoo,

One way is to put the pounds in one column (say A), and the ounces in
another (say B). Sum them with:

For the pounds: =INT(SUM(A2:A10) + SUM(B2:B10)/16)
For the ounces: =MOD(SUM(A2:A10) + SUM(B2:B10)/16,1)*16

 

[Dave Peterson]

I like Earl's suggestion of putting the different units into different fields,
but if you didn't...

These are all array formulas. That means you hit ctrl-shift-enter instead of
enter. If you do it correctly, excel will wrap curly brackets {} around your
formula. (don't type them yourself.)

This will add up the whole numbers in your range:
=SUM(INT(A1:A10))
This will add up the ounces and find out how many pounds (carry the x stuff):
=INT(SUM(MOD(A1:A10,1))*100/16)
This will add up just the ounces portion (no carrying)
=MOD(SUM(MOD(A1:A10,1))*100/16,1)*16

So you could use a giant formula like:

=SUM(INT(A1:A10))+INT(SUM(MOD(A1:A10,1))*100/16)
+(MOD(SUM(MOD(A1:A10,1))*100/16,1)*16/100)

(all one cell, and remember to hit ctrl-shift-enter)

But the bad news is that there can be some rounding errors. This worked better
for me in my testing:

Whole numbers:
=SUM(INT(A1:A10))
pounds in the ounce total:
=INT(ROUND(SUM(MOD(A1:A10,1)),3)*100/16)
ounces in the ounce total:
=MOD(ROUND(SUM(MOD(A1:A10,1)),3)*100/16,1)*16

And as a giant formula:
=SUM(INT(A1:A10))+INT(ROUND(SUM(MOD(A1:A10,1)),3)*100/16)
+MOD(ROUND(SUM(MOD(A1:A10,1)),3)*100/16,1)*16

 

[Sandy Mann]

Agoogirl,

As an academic exercise, with the data in G1:G12 in the form that you gave
(ie 6.02 etc.) then

=INT(SUM(INT(G1:G12))+SUM(MOD(G1:G12,1)*100)/16)+MOD(SUM(MOD(G1:G12,1)*100),
16)/100

entered as an array formula by pressing Control + Shift + Enter intead of
just enter will give you the answer you seek but I do not recommend it - go
with Earl's solution.

Regards

Sandy