Adding pounds and ounces

[garygarris]

How do I format cells so that when I sum them together they will
convert 16oz to a pound. the example is 10 lbs 6 oz plus 10 lbs 10 oz
equals 21 lbs not 20 lb 16 oz. I'm trying to calculate fishing weights
for the highest total in pounds and ounces. Thanks

 

[PhilD]

How do I format cells so that when I sum them together they will
convert 16oz to a pound. the example is 10 lbs 6 oz plus 10 lbs 10 oz
equals 21 lbs not 20 lb 16 oz. I'm trying to calculate fishing weights
for the highest total in pounds and ounces. Thanks

One possibility is to enter your weights as X+Y/16, where X is pounds
and Y is ounces. This will convert to decimal pounds. You can then
use other formulae if necessary to display the total as separate
pounds/ounces.

Something like this (assume that the decimal total is in cell A1):

=concatenate(int(A1),"lb ",((A1-Int(A1))*16),"oz")

I may have put more parenthases than absolutely necessary, but it
works.

If you put pounds and ounces in separate cells (say A1, B1), it's
easier to display the figures with units:

=concatenate(A1,"lb ",B1,"oz")

Does this help?

PhilD

 

[Bob Greenblatt]

One possibility is to enter your weights as X+Y/16, where X is pounds
and Y is ounces. This will convert to decimal pounds. You can then
use other formulae if necessary to display the total as separate
pounds/ounces.

Something like this (assume that the decimal total is in cell A1):

=concatenate(int(A1),"lb ",((A1-Int(A1))*16),"oz")

I may have put more parenthases than absolutely necessary, but it
works.

If you put pounds and ounces in separate cells (say A1, B1), it's
easier to display the figures with units:

=concatenate(A1,"lb ",B1,"oz")

Does this help?

PhilD

Just format the cells a 2 digit fractions. Enter the first number as 10
6/16, Excel will round it to 10 3/8, and the second as 10 10/16. Likewise
this will be rounded/ Add the 2 together and the sum is 21.

 

[PhilD]

Just format the cells a 2 digit fractions. Enter the first number as 10
6/16, Excel will round it to 10 3/8, and the second as 10 10/16. Likewise
this will be rounded/ Add the 2 together and the sum is 21.

Oooh, yes, much better. I've never really looked at number formats in
anger (I usually only use custom so as to display leading zeros). I've
just had a look and I know now how to simplify lots of calculations for
work (where I have to use eightieths a lot).

Thanks,

PhilD

 

[TR]

This is the kind of thing that makes it worth skimming through these
posts from time to time. I had no idea that Excel could do this, and
I've written some pretty complex formulae to do it in the past. Life
will be easier in the future.

Thanks.

 

[Bob Greenblatt]

This is the kind of thing that makes it worth skimming through these
posts from time to time. I had no idea that Excel could do this, and
I've written some pretty complex formulae to do it in the past. Life
will be easier in the future.

Thanks.

Oh, and if you do not want Excel to round off the fractions, but to always
display them in eights for example, format the cells as: 0 ?/8.