Running total of lbs and ozs

[andymo56]

Hi I am trying to run a a league table adding peoples weights each week so agter each week we add there weights over a 12 week period it is in lbs and ozs can it be done. Thanks.
493AFE3C-CDE9-4DB2-B801-D2CC833FF9E0.jpeg. Ha attached a picture of the kind thing I am after hope I am doing 5his in the right way.

[AliGW]

It depends entirely how you are recording them, so more information, please.

[andymo56]

Hi AlliGW hope the picture that I have done is of some help.

[andymo56]

Hi AlliGW hope picture helps

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[andymo56]

fishing.xlsx
Hope this is of some help

[MrShorty]

Unless there is some external requirement to enter the values as text like that, I would go to some lengths to be able to enter the values as numbers.

1) You could enter fractions 4 7/16 -- which Excel should recognize as a fractional number and enter the actual number as decimal lbs. A number format like "0 0/16" would display the decimal pounds as lbs ozs/16.
2) You could enter something like 4.07 -- meaning 4 lbs and 7 ozs, then use the DOLLARDE() and DOLLARFR() functions to convert to decimal lbs.
3) Enter lbs and ozs (without the unit text) into separate cells, then add them together to get either decimal pounds or total ozs.
4) If you really must enter the text string like you show, you can use Excel's text manipulation functions (like LEFT(), RIGHT(), MID()) to extract the numbers from the text string, but I tend to avoid that sort of thing at all costs.

Bottom line -- Excel is going to want to do its computations on a real number -- either decimal pounds or total ozs. Once you convert your input into a number, the sum can be either a + operator or a SUM() function.

Are you only considering the text input you show, or which input format are you willing to explore?

[andymo56]

No i don’t mind I could do it insisted of 4lb 7oz I could do 4.07 I only put the lb and oz in there to show what I was trying to do.

[MrShorty]

Using a lbs.ozs input like that, you would:

0) In the table, I entered the values as lbs.ozs (4.07, 3.04, 0, 6.03, etc.)
1) In a helper cell/table, enter =DOLLARDE(input cell,16) to convert to decimal pounds. In your sample file, I entered =DOLLARDE(B2,16) in H2 and copied down and across.
2) In L2, I entered my summing function =SUM(H2:L2), copied down
3) In F2, the final output cell, I entered =DOLLARFR(L2,16) to convert the summation back to the original lbs.ozs format.
4) If you find the helper table in columns H:L offensive, they can be hidden.

That's one way I would do it.

DOLLARDE() helpfile: https://support.office.com/en-us/art...d-a38476693427
DOLARFR() helpfile: https://support.office.com/en-us/art...4-3042c5d4f495

[andymo56]

Thanks will give it a try Friday when get back to my laptop