# Thread: LBS OZ formula Thanks:  1 Post #5323008 (1) Likes:  1 Post #5323008 (1)

1. ## LBS OZ formula

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.

2. ## Re: LBS OZ formula

 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)

3. ## Re: LBS OZ formula

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.3 1.76875 3.7 0.23125 0.1 0.00625 1.5 0.09375 2.1

4. ## Re: LBS OZ formula

Maybe
ABC
1112.31.768752.1
23.70.23125
30.10.00625
41.50.09375
52.1

Sheet1

Array Formulas
CellFormula
C1{=SUM(DOLLARDE(A1:A4/100,16))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. ## Re: LBS OZ formula

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.3 2.2125 3.7 0.1 1.5 0.9 0.9

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

Thanks again.

6. ## Re: LBS OZ formula

=DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
Also array entered

7. ## Re: LBS OZ formula

Originally Posted by Fluff
=DOLLARFR(SUM(DOLLARDE(A5:A10/100,16)),16)
Also array entered
It looks like its working perfect. Thank you very much!

8. ## Re: LBS OZ formula

Glad we could help & thanks for the feedback

9. ## Re: LBS OZ formula

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?

10. ## Re: LBS OZ formula

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