# Thread: Formula to convert Oz back into autosum and add to left of decimal place Thanks: 0 Likes: 0

1. ## Formula to convert Oz back into autosum and add to left of decimal place

Help please,

I have a fishing club workbook that I have to sit and work out in Lbs and Ounces.

I have the format for cells as ustom 0" lb". 00" oz."

Once autosum has been applied to a range it will give a total in LBs and Ounces but counts all of the ounces up.

Is there a formula that anyone knows where I can automatically divide the ounces by 16 and any lbs moved to the left of decimal place and remaining ounces shown to the right.

Final sum required would be 34lb. 6oz. if this makes sense   Reply With Quote

2. ## Re: Formula to convert Oz back into autosum and add to left of decimal place

Here is a sample

 v A B C D 1 Total Oz Lbs Oz Combined 2 30 1 14 1lb 14oz 3 17 1 1 1lb 1oz 4 21 1 5 1lb 5oz

and here are the formulas

 v A B C D 1 Total Oz Lbs Oz Combined 2 30 =INT(A2/16) =MOD(A2,16) =CONCATENATE(B2,"lb"," ",C2,"oz") 3 17 =INT(A3/16) =MOD(A3,16) =CONCATENATE(B3,"lb"," ",C3,"oz") 4 21 =INT(A4/16) =MOD(A4,16) =CONCATENATE(B4,"lb"," ",C4,"oz")  Reply With Quote

3. ## Re: Formula to convert Oz back into autosum and add to left of decimal place

[QUOTE=alansidman;5312925]Here is a sample

 v A B C D 1 Total Oz Lbs Oz Combined 2 30 1 14 1lb 14oz 3 17 1 1 1lb 1oz 4 21 1 5 1lb 5oz

and here are the formulas

 v A B C D 1 Total Oz Lbs Oz Combined 2 30 =INT(A2/16) =MOD(A2,16) =CONCATENATE(B2,"lb"," ",C2,"oz") 3 17 =INT(A3/16) =MOD(A3,16) =CONCATENATE(B3,"lb"," ",C3,"oz") 4 21 =INT(A4/16) =MOD(A4,16) =CONCATENATE(B4,"lb"," ",C4,"oz")

Alan,

Many thanks for the formula which I like but is there a formula that can autosum the Total weights i.e
 1lb 14oz 1lb 1oz 1lb 5oz which using autosum gives 3lbs 20oz and what i need to do then is convert the 20oz to read 1lb 4oz and add to the total of the lbs which would give me 4lb 4oz if you get what I am trying to achieve or is it easier to autosum column B and C and do the calculation against the total Row to combine all data as a total  Reply With Quote

4. ## Re: Formula to convert Oz back into autosum and add to left of decimal place

You could give an example of how your data is on your sheet and what you expect from the result.
I'm not understanding, in B you have lb or oz. In C you have lb or oz.
Or what exactly do you have?  Reply With Quote

5. ## Re: Formula to convert Oz back into autosum and add to left of decimal place

easier to autosum column B and C and do the calculation against the total Row to combine all data as a total
Seems like a reasonable way to do it. Keep it simple. Why overthink the issue.  Reply With Quote

6. ## Re: Formula to convert Oz back into autosum and add to left of decimal place

Maybe something like

JK
23.153.9375
31.141.875
41.011.0625
56.14

Sheet1

Worksheet Formulas
CellFormula
K2=DOLLARDE(J2,16)
K5=DOLLARFR(SUM(K2:K4),16)  Reply With Quote

## User Tag List

#### Tags for this Thread

formula, lbs, left, ounces, place #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•