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

 

[Raively]

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

Lets say I'm new to this. Where would I type this code in? tried it in M16 for the total and all I got was #####. I'm lost now!!!! Help...

 

[Raively]

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.

Ok how would I do this??? I went to format cell clicked on text and it shows it as 3.1 not 3.10. When I formated the cell to number 2 decimal it changed it to 3.10 but still added at as 3.1. With your formula the total was 19lbs 2oz. It was reading the 3.10 as 3.1..

Thanks
Bob

 

[Raively]

mmyett Thank you, I wasn't thinking I had to cell the cell with the 3.1 in it formatted the cell to text then typed in 3.10 and it works..

Thanks Again

Bob

 

[mmyett]

Your welcome. Have a good weekend.

 

[Raively]

Your welcome. Have a good weekend.

Thank You..

One more thing Right know I have 7.8 in C16. I used D16 and E16 as a example. When I erased those values I get #### in M16.

As the fishing season goes on I will be entering values in D16,E16,F16,G16,H16,I16,J16,K16, and L16 the total will be in M16.

If I enter 0.0 in D16 and E16 then M16 Reads 7lbs 8oz. if I take them out I get the error. Do I have to have 0.0 values in those cells to make it work??

Thanks
Bob

 

[CWatts]

Lets say I'm new to this. Where would I type this code in? tried it in M16 for the total and all I got was #####. I'm lost now!!!! Help...

Push alt F11 that brings up the VBA window. Go to insert, and then choose module.

You should get a white box where you can paste that code in. Once that's done, then just hit the X to close that window and you'll be back to your excel screen. The benefit to using the custom function is that the cell formulas are really easy to manipulate.

The draw back is that you'll have to make sure you enable macros and if it's excel 2007 or later, you'll have to save it as a macro-enabled workbook (.xlsm).

It doesn't handle the 10 oz problem well though so I wouldn't bother at this point.

 

[Joe4]

I was away for a few hours and missed all the fun!

mmyett Thank you, I wasn't thinking I had to cell the cell with the 3.1 in it formatted the cell to text then typed in 3.10 and it works..

Yes, a zero on the end of a decimal numeric entry will be dropped, as 3.10 and 3.1 equal the same thing, in numeric terms. Changing it to text keeps all characters (but then of course it is a "text" entry and not a "numeric" one).

If it was myself, I probably would have kept it numeric, and entered 3 lbs 1 oz as 3.01 instead of 3.1. Then it can easily be differentiated from 3 lbs 10 oz and kept numeric. So all your numbers would have two decimal places, making the math work easier. Of course, the solutions you have now would have to be adjusted some then.

Just my two cents, FWIW...

 

[CWatts]

That or "3 10" with a space in between.

I'd just rather we ditch the entire measuring system and go to metric.

 

[mmyett]

Thank You..

One more thing Right know I have 7.8 in C16. I used D16 and E16 as a example. When I erased those values I get #### in M16.

As the fishing season goes on I will be entering values in D16,E16,F16,G16,H16,I16,J16,K16, and L16 the total will be in M16.

If I enter 0.0 in D16 and E16 then M16 Reads 7lbs 8oz. if I take them out I get the error. Do I have to have 0.0 values in those cells to make it work??

Thanks
Bob

What version of Excel are you using? if you are using 2007 or newer just wrap the formula in the IFERROR function, with "" as the result if there is an error.

 

[mmyett]

...