LBS OZ formula

RustyJOliver

New Member
Joined
Aug 9, 2019
Messages
6
Hi, I have done a lot of searching everywhere and came up with a few different ways of doing this, but the final formula that started working for me needs help. Here is what I need:

I can only export the data i need from a price field ie: 0.00 due to the limitations of the program I use. A lot of the weight i need to calculate is less than 1oz.

So I have entered in the price field 112.3 this comes out to 1lb 12.3oz next line entered at 3.7 (3.7oz) this should come out to 2lbs total but i the formula is producing 2lbz 16oz until i add another oz then the 16oz goes away correctly.

Because I am very rusty with formula's i have only been able to create my weights in A1:A300 then in B1 i have =sum(A1:A300)/100 producing (1.16)
then in C1 i have:

=INT(SUM(INT(B1))+SUM(MOD(B1,1)*100)/16)+MOD(SUM(MOD(B1,1)*100),16)/100

hope this makes sense if not just yell at me.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
A​
B​
C​
1​
data
lbs
2​
112.3​
1.769​
B2: =DOLLARDE(A2/100, 16)
3​
3.7​
0.231​
4​
2.000​
B4: =SUM(B2:B3)
 
Upvote 0
Thanks for the reply, i was trying to get away from copying a formula down (Not a big deal) but the sum does not go down to the 10th's i need to full weight.

112.31.76875
3.70.23125
0.10.00625
1.50.09375
2.1

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Maybe

Book1
ABC
1112.31.768752.1
23.70.23125
30.10.00625
41.50.09375
52.1
Sheet1
Cell Formulas
RangeFormula
C1{=SUM(DOLLARDE(A1:A4/100,16))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Sorry forgot to explain the reply. I was getting 2.1 instead of 2.01.6 like my formula generates. and this happens when adding more:

112.32.2125
3.7
0.1
1.5
0.9
0.9

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

The correct results i need would be 2.03.4 (2.LBs 03.4Oz) but its giving 2.2125

Thanks again.
 
Upvote 0
In that case how about
=DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
Also array entered
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Not sure what happened but see if this makes sense. In real life application I had a total in column A of 51.95 (5195)/100 the formula is showing 62lbs 03.0Oz

Actual 62.1875 with the other formula you showed me =SUM(DOLLARDE(A1:A400/100,16))

Shouldn't it be showing 63lbs 02.7oz?
 
Upvote 0
If your total is 51lbs 95oz I make that 56lbs 15oz
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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