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)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[chunu]

Account details updated

 

[bebo021999]

it reflect exactly whay you did, but simpler process:

=INT(SUMPRODUCT(A2:A8*B2:B8))+MOD(SUMPRODUCT(A2:A8*B2:B8),1)*100/12

=160

 

[Peter_SSs]

it reflect exactly whay you did, but simpler process:

=INT(SUMPRODUCT(A2:A8*B2:B8))+MOD(SUMPRODUCT(A2:A8*B2:B8),1)*100/12

=160

Not sure that it is quite that simple?

21 12 10.xlsm
	A	B
1	Qty	Size
2	4	2.06
3	3	3.06
4	4	5.08
5	17	3.09
6	4	4.04
7	6	8.11
8	5	9.02
9	43	201.07
10		201.5833
chunu

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)
B10	B10	=INT(SUMPRODUCT(A2:A8*B2:B8))+MOD(SUMPRODUCT(A2:A8*B2:B8),1)*100/12

 

[Peter_SSs]

Account details updated

Thanks.

There may be some more shortening possible but with my limited testing this seems to produce the same results.

=TRUNC(TRUNC(SUMPRODUCT(A2:A8,B2:B8))+MOD(SUMPRODUCT(A2:A8,B2:B8),1)*25/3)+(TRUNC(SUMPRODUCT(A2:A8,B2:B8))
+MOD(SUMPRODUCT(A2:A8,B2:B8),1)*25/3-TRUNC(TRUNC(SUMPRODUCT(A2:A8,B2:B8))+MOD(SUMPRODUCT(A2:A8,B2:B8),1)*25/3))*0.12

 

[Dave Patton]

What does 2.06 represent. Does this number represents feet and inches? If yes, the calculation would be different.
What are you are you trying to calculate? Is the 160 correct?

 

[chunu]

What does 2.06 represent. Does this number represents feet and inches? If yes, the calculation would be different.
What are you are you trying to calculate? Is the 160 correct?

Yes 2.06 represents feet and inches.
160 is example only.
If calculate 2.06+2.03 with formula suggested by "bebo021999" result will be 4.75 which correct but i would like see as 4.09 in the cell for some reason,easy to understand as 4feet 9inches.
Thanks

 

[Dave Patton]

The formula and results will depend on how you will use the information.
The post below may give you some ideas that you can use.

T202112a.xlsm
	A	B	C	D	E
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			156	feet
10
11	1	2.03
12	1	2.05
13	1	2.06		7.17	feet
14
15		8	feet
16		6	feet	14	inches
17		7	feet	2	inches
18		7 ft 2 in
2a

Cell Formulas
Range	Formula
A9	A9	=SUM(A2:A8)
D9	D9	=SUMPRODUCT(A2:A8,INT(B2:B8))+CEILING(SUMPRODUCT(MOD(B2:B8,1)*100/12),1)
D13	D13	=SUMPRODUCT(A11:A13,INT(B11:B13))+SUMPRODUCT(MOD(B11:B13,1)*100/12)
D16	D16	=SUMPRODUCT(MOD(B11:B13,1)*100)
D17	D17	=MOD(SUMPRODUCT(MOD(B11:B13,1)*100),12)
B15	B15	=SUMPRODUCT(A11:A13,INT(B11:B13))+CEILING(SUMPRODUCT(MOD(B11:B13,1)*100/12),1)
B16	B16	=SUMPRODUCT(A11:A13,INT(B11:B13))
B17	B17	=SUMPRODUCT(A11:A13,INT(B11:B13))+INT(SUMPRODUCT(MOD(B11:B13,1)*100)/12)
B18	B18	=SUMPRODUCT(A11:A13,INT(B11:B13))+INT(SUMPRODUCT(MOD(B11:B13,1)*100)/12)&" ft "&ROUND(MOD(SUMPRODUCT(MOD(B11:B13,1)*100),12),0)&" in"

 

[Dave Patton]

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