LBS OZ formula

[RustyJOliver]

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.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

data​

[/td][td="bgcolor:#F3F3F3"]

lbs​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

112.3​

[/td][td="bgcolor:#CCFFCC"]

1.769​

[/td][td="bgcolor:#CCFFCC"]B2: =DOLLARDE(A2/100, 16)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

3.7​

[/td][td="bgcolor:#CCFFCC"]

0.231​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td="bgcolor:#CCFFFF"]

2.000​

[/td][td="bgcolor:#CCFFFF"]B4: =SUM(B2:B3)[/td][/tr]
[/table]

 

[RustyJOliver]

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.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]112.3[/TD]
[TD="class: xl63, width: 64, align: right"]1.76875[/TD]
[/TR]
[TR]
[TD="align: right"]3.7[/TD]
[TD="class: xl63, align: right"]0.23125[/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD="class: xl63, align: right"]0.00625[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="class: xl63, align: right"]0.09375[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]2.1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Maybe

Book1
	A	B	C
1	112.3	1.76875	2.1
2	3.7	0.23125
3	0.1	0.00625
4	1.5	0.09375
5		2.1
Sheet1

Cell Formulas
Range	Formula
C1	{=SUM(DOLLARDE(A1:A4/100,16))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RustyJOliver]

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:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]112.3[/TD]
[TD="class: xl65, width: 64, align: right"]2.2125[/TD]
[/TR]
[TR]
[TD="align: right"]3.7[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks again.

 

[Fluff]

In that case how about
=DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
Also array entered

 

[RustyJOliver]

In that case how about
=DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
Also array entered

It looks like its working perfect. Thank you very much!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[RustyJOliver]

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?

 

[Fluff]

If your total is 51lbs 95oz I make that 56lbs 15oz