Trunc Multiply columns and Sum

[chunu]

Hi,
How can i simplify this formula. i have tried sumproduct but could no succeed.

thanks

Calculation9 (1).xlsx
	A	B
1	Qty	Size
2	4	2.06
3	3	3.06
4	4	5.08
5	2	3.09
6	4	4.04
7	6	8.11
8	5	9.02
9	28	160
cutting

Cell Formulas
Range	Formula
A9	A9	=SUM(A2:A8)
B9	B9	=TRUNC(SUM(TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0)+((A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8-TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0))*100)/12),0)+((SUM(TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0)+((A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8-TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0))*100)/12)-TRUNC(SUM(TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0)+((A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8-TRUNC(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8,0))*100)/12),0))*12/100)

 

[Dave Patton]

edit post 10

T202112a.xlsm
	A	B	C	D
11	3	2.03
12	2	2.05
13	2	2.06		16.07
2a

Cell Formulas
Range	Formula
D13	D13	=SUMPRODUCT(A11:A13,INT(B11:B13))+DOLLARFR(SUMPRODUCT(A11:A13,MOD(B11:B13,1))*100/12,12)

 

[chunu]

=DOLLARFR(SUMPRODUCT(A11:A13,INT(B11:B13))+SUMPRODUCT(MOD(B11:B13,1)*100/12),12) yields 7.02

Thank you so much both formula worked perfectly.

 

[chunu]

edit post 10

T202112a.xlsm
	A	B	C	D
11	3	2.03
12	2	2.05
13	2	2.06		16.07
2a

Cell Formulas
Range	Formula
D13	D13	=SUMPRODUCT(A11:A13,INT(B11:B13))+DOLLARFR(SUMPRODUCT(A11:A13,MOD(B11:B13,1))*100/12,12)

Thanks
This one perfect.