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

J

Jacko1307

Board Regular
Joined
Sep 4, 2012
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Here is a sample

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Total Oz[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Lbs[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Oz[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Combined[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]30[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1lb 14oz[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1lb 1oz[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]21[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1lb 5oz[/td]
[/tr]
[/table]

and here are the formulas

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Total Oz[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Lbs[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Oz[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Combined[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]30[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=INT(A2/16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=MOD(A2,16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=CONCATENATE(B2,"lb"," ",C2,"oz")[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=INT(A3/16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=MOD(A3,16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=CONCATENATE(B3,"lb"," ",C3,"oz")[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]21[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=INT(A4/16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=MOD(A4,16)[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=CONCATENATE(B4,"lb"," ",C4,"oz")[/td]
[/tr]
[/table]
 
Upvote 0
alansidman said:
Here is a sample

[TABLE="class: thin_grid"]
<tbody>[TR]
[TD]v[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Total Oz[/TD]
[TD="bgcolor: #FFFFFF"]Lbs[/TD]
[TD="bgcolor: #FFFFFF"]Oz[/TD]
[TD="bgcolor: #FFFFFF"]Combined[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]30[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]14[/TD]
[TD="bgcolor: #FFFFFF"]1lb 14oz[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]17[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]1lb 1oz[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]21[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]5[/TD]
[TD="bgcolor: #FFFFFF"]1lb 5oz[/TD]
[/TR]
</tbody>[/TABLE]

and here are the formulas

[TABLE="class: thin_grid"]
<tbody>[TR]
[TD]v[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Total Oz[/TD]
[TD="bgcolor: #FFFFFF"]Lbs[/TD]
[TD="bgcolor: #FFFFFF"]Oz[/TD]
[TD="bgcolor: #FFFFFF"]Combined[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]30[/TD]
[TD="bgcolor: #FFFFFF"]=INT(A2/16)[/TD]
[TD="bgcolor: #FFFFFF"]=MOD(A2,16)[/TD]
[TD="bgcolor: #FFFFFF"]=CONCATENATE(B2,"lb"," ",C2,"oz")[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]17[/TD]
[TD="bgcolor: #FFFFFF"]=INT(A3/16)[/TD]
[TD="bgcolor: #FFFFFF"]=MOD(A3,16)[/TD]
[TD="bgcolor: #FFFFFF"]=CONCATENATE(B3,"lb"," ",C3,"oz")[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]21[/TD]
[TD="bgcolor: #FFFFFF"]=INT(A4/16)[/TD]
[TD="bgcolor: #FFFFFF"]=MOD(A4,16)[/TD]
[TD="bgcolor: #FFFFFF"]=CONCATENATE(B4,"lb"," ",C4,"oz")[/TD]
[/TR]
</tbody>[/TABLE]

Alan,

Many thanks for the formula which I like but is there a formula that can autosum the Total weights i.e
[TABLE="class: thin_grid"]
<tbody>[TR]
[TD="bgcolor: #FFFFFF"]1lb 14oz[/TD]
[TD="bgcolor: #FFFFFF"]1lb 1oz[/TD]
[TD="bgcolor: #FFFFFF"]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

[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Last edited:
Upvote 0
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?
 
Upvote 0
easier to autosum column B and C and do the calculation against the total Row to combine all data as a total
Click to expand...
Seems like a reasonable way to do it. Keep it simple. Why overthink the issue.
 
Upvote 0
Maybe something like


Book1
JK
23.153.9375
31.141.875
41.011.0625
56.14
Sheet1
Cell Formulas
RangeFormula
K2=DOLLARDE(J2,16)
K5=DOLLARFR(SUM(K2:K4),16)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
508
AlfredDB
A
T
  • Question Question
Converting number to fixed length field
Replies
8
Views
26K
Joe4
Joe4
I
  • Question Question
Formula to move decimal point 2 places to the left
Replies
4
Views
20K
oldbrewer
O
C
  • Question Question
Add Extra Zeros in a formula
Replies
3
Views
564
Nine Zero
N
N
  • Question Question
convert hours into decimals
Replies
3
Views
391
FDibbins
FDibbins

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top