I don't think ANYONE can add pounds and ounces!

[1fishy]

I'm trying to do a weigh in sheet for fishing tournaments. I can't fin
good formulas to add pounds and ounces. Seems that math based on 1
instead of 10's is a big problem?? I need help bad!
Here's the conversion fomula I was able to find.

=INT(CONVERT(C7,"ozm","lbm"))&" lbs " &INT
MOD(CONVERT(C7,"ozm","ozm"),16))& " oz"

It doesn't work properly either. When the ounces add up to multiples o
16 it doesn't add them to the pounds.

I wish somebody could at least explain all that this formula is doin
so I might be able work it out. What does the MOD do? And how is thi
formula moving the ounces to the pounds column with the remainder i
the ounces column? I understand the ambersand, and Convert, but wha
does the INT (integer?) do?
HELP, HELP, HELLLLLPPPP

 

[JMay]

I worked up a sample worksheet of how I'd do it.
I will gladly send it to you on request.
Furnish e-mail address for mailing if desired..

 

[Arvi Laanemets]

Hi

This formula returns text string like '2 lbs 4 ozm'. It isn't number at all,
and you can't do any calculations with it - at least without converting it
back to number again.

 

[Norman Harker]

Hi 1Fishy!

Assuming entries in A1:A3 like

1.13 for 1 pound 3 ounces
2.05 for 2 pounds 5 ounces
3.15 for 3 pounds 15 ounces

A formula for summing and showing in the same format could be:

=SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1))*100/16)+MOD(SUM(MOD(A1:A3,1))*100/16,1)*16/100
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing enter.

Explanation? We don't like just giving solutions here.

You have three main elements here which we add together:
=SUM(INT(A1:A3))
=INT(SUM(MOD(A1:A3,1))*100/16)
=MOD(SUM(MOD(A1:A3,1))*100/16,1)*16/100


First Element:
=SUM(INT(A1:A3))
(Array entered) returns the sum of the integers.

Derive Second element:
=SUM(MOD(A1:A3,1))
(Array entered) returns the sum of the non-integer parts

=SUM(MOD(A1:A3,1))*100/16
(Array entered) returns the sum of the non-integers converted to
pounds

=INT(SUM(MOD(A1:A3,1))*100/16)
(Array entered) returns the number of pounds in the sum of the
non-integers converted to pounds.

Derive Third element:
=SUM(MOD(A1:A3,1))*100/16
(Array entered) returns the sum of the non-integers converted to
pounds

=MOD(SUM(MOD(A1:A3,1))*100/16,1)
(Array entered) returns the non-integer part of the sum of
non-integers converted to pounds

MOD(SUM(MOD(A1:A3,1))*100/16,1)*16/100
(Array entered) returns the non-integer part of the sum of
non-integers converted to ounces and then divide by 100 (so that .12
represents 12 ounces)


I've used an assumption at the beginning on mode of entry. If you have
something different such as a text form of entry, I'm sure that we can
do the simple task of parsing it and adding. We might need helper
columns to make it easier.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

I'm trying to do a weigh in sheet for fishing tournaments. I can't find
good formulas to add pounds and ounces. Seems that math based on 16
instead of 10's is a big problem?? I need help bad!
Here's the conversion fomula I was able to find.

=INT(CONVERT(C7,"ozm","lbm"))&" lbs " &INT(
MOD(CONVERT(C7,"ozm","ozm"),16))& " oz"

It doesn't work properly either. When the ounces add up to multiples of
16 it doesn't add them to the pounds.

I wish somebody could at least explain all that this formula is doing
so I might be able work it out. What does the MOD do? And how is this
formula moving the ounces to the pounds column with the remainder in
the ounces column? I understand the ambersand, and Convert, but what
does the INT (integer?) do?
HELP, HELP, HELLLLLPPPP!

One method if you have the Analysis Tool Pack installed:

Assuming entries in A1:A3 like

1.13 for 1 pound 13 ounces
2.05 for 2 pounds 5 ounces
3.15 for 3 pounds 15 ounces

Set up a 'helper' column.

Convert to decimal numbers with the formula:

=DOLLARDE(A1,16)

Sum the helper column and convert back to pounds/ounces with:

=DOLLARFR(SUM(B1:B3),16)

If you don't mind seeing the errant decimal point, you could custom format the
lb/oz cells as 0"lb ".00"oz"


--ron

 

[JMay]

Ron thanks for the function;
I have:
B F
10 3.13 =DOLLARDE(B10,16)
11 4.01 copied down to F13
12 4.13
13 1.05


In cell B14

=DOLLARFR(SUM(F10:F13),16) produces --->>> 13.16, not 14.00
How can I modify to get the 14.00?
TIA,

 

[Ron Rosenfeld]

Ron thanks for the function;
I have:
B F
10 3.13 =DOLLARDE(B10,16)
11 4.01 copied down to F13
12 4.13
13 1.05


In cell B14

=DOLLARFR(SUM(F10:F13),16) produces --->>> 13.16, not 14.00
How can I modify to get the 14.00?
TIA,

Hmmm. It seems you have run into one of those errors that crops up when
converting between binary--decimal numbers.

One way to correct the problem, although I have not tested it extensively,
would be to round your SUM.

e.g. =DOLLARFR(ROUND(SUM(F10:F13),n),16)

where 'n' is your desired precision. n=4 should work OK.



--ron

 

[JMay]

Thanks Ron;
The problem disappeared.

Hmmm. It seems you have run into one of those errors that crops up when
converting between binary--decimal numbers.

One way to correct the problem, although I have not tested it extensively,
would be to round your SUM.

e.g. =DOLLARFR(ROUND(SUM(F10:F13),n),16)

where 'n' is your desired precision. n=4 should work OK.



--ron

 

[Ron Rosenfeld]

Thanks Ron;
The problem disappeared.

You're welcome. Thanks for the feedback.


--ron

 

[1fishy]

I cannot thank _All_Of_You_ enough! I realize what I'm asking isn't fo
business or brain surgery. Just a little old fishing tournamen
weigh-in sheet. If it's any consolation, I will share this with man
different clubs having the same problems. Bass fishermen do not like t
convert decimals. Who knows it may be spread around nationally or eve
internationally. I recently took an intermediate Excel class at work
The instructors absolutely drew a blank with this. They didn't eve
know about this site??!
I've learned more reading posts here than I did in class. Most of wha
they taught I figured out myself between making mistakes and let's se
what this button does. I haven't had time to try any of this out yet.
hope I have time this week. I get back with you. Thanks again!

P.S. J May I very much would like to see your sample worksheet.
Here's my address (e-mail address removed)