Fishing Again Pounds and Ounces

[Raively]

Hello all,

Where we go again. I've searched the site to find something to help me out but just don't see it...

This is what I need..

In C16 I have 7.8(which is 7pounds and 8oz's)
In D16 I have 8.9
In E16 I have 3.10

And so on and so on.

In M16 in I would like the total for C16-L16 to read 19.11
So basically when it adds the row up every 16oz converts to a pound.
Then the total in M16.
After reading some of the post if you can make M16 read 19lbs 11oz, That would be great but I know theres more work involed to do that.If not 19.11 will work for me..

Thanks for all the help.
Bob

 

[UniMord]

Enter the following formula, and confirm it with Control + Shift + Enter (not just enter).

=TRUNC(SUM(TRUNC(C16:E16)*16, (C16:E16 - TRUNC(C16:E16))*10) / 16) & "lbs " & MOD(SUM(TRUNC(C16:E16)*16, (C16:E16 - TRUNC(C16:E16))*10), 16) & "oz"

 

[Joe4]

UniMord,

I have been messing around with this for a few minutes, and saw your reply.
It looks pretty close, but it appears to be a little off. It appears to be returning "19 lbs 2 oz" instead of "19 lbs 11 oz".

 

[UniMord]

I believe my numbers are correct. Let's add them up.

7lbs + 8lbs + 3lbs = 18lbs
8oz + 9oz + 1oz =18oz = 1lb 2oz
18lbs. + 1lb 2oz = 19lbs 2oz

 

[mmyett]

Give this a try.

=SUMPRODUCT(LEFT(C16:E16,FIND(".",C16:E16,1)-1)*1)+INT(SUMPRODUCT(MID(C16:E16,FIND(".",C16:E16,1)+1,99999)*1)/16)&" LBS "&MOD(SUMPRODUCT(MID(C16:E16,FIND(".",C16:E16,1)+1,99999)*1),16)&" OZ"

 

[Raively]

Thank you, I will give it a try.

And 8oz + 9oz + 1oz =18oz = 1lb 2oz

This was 8oz + 9oz + 10oz would = 1lb 11oz.

Let me try this will be right back. Thanks

 

[Joe4]

I believe my numbers are correct. Let's add them up.

7lbs + 8lbs + 3lbs = 18lbs
8oz + 9oz + 1oz =18oz = 1lb 2oz
18lbs. + 1lb 2oz = 19lbs 2oz

I think I see the problem.

In the original problem, it looks like he intended 3.10 to represent 3 lbs 10 oz, but it looks like you are treating it as 3 lbs 1 oz.

 

[Raively]

UniMord I tried to copy and paste you formula in and I get #### in the cell.

mmyett copied and pasted your formula in and it worked to a degree.

Heres my next question I see what UniMord was talking about 3.1 should be 3.10 do I have to format the cell to something so it will read 3.10? and does the total have to be formated to also read right??

Thanks

Bob

 

[CWatts]

Yeah, the differentiation between 3.1 and 3.10 is going to cause a lot of problems.

I worked on a worksheet function to do the calculations as an alternative to large formulas using the following syntax:

=Lboz(C16:L16)

Function LbOz(rng As Range)
Dim pounds As Integer
Dim ounces As Integer
 
pounds = 0
ounces = 0
 
For Each cell In rng
    ounces = ounces + Right(cell, Len(cell) - InStr(cell, "."))
    pounds = pounds + Int(cell.Value)
Next cell
 
pounds = pounds + Int(ounces / 16)
ounces = ounces - Int(ounces / 16) * 16
 
LbOz = (pounds & "." & ounces) * 1
 
End Function

 

[mmyett]

mmyett copied and pasted your formula in and it worked to a degree.

To what degree? Yes the cells being worked on should be formatted as text to preserve the trailing zeros.

It is working fine here.