# Trunc Multiply columns and Sum

#### chunu

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

thanks

Calculation9 (1).xlsx
AB
1QtySize
242.06
333.06
445.08
523.09
644.04
768.11
859.02
928160
cutting
Cell Formulas
RangeFormula
A9A9=SUM(A2:A8)
B9B9=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)

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Board Regular
Account details updated

#### bebo021999

##### Well-known Member
it reflect exactly whay you did, but simpler process:

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

#### Peter_SSs

##### MrExcel MVP, Moderator
it reflect exactly whay you did, but simpler process:

Code:
``=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
AB
1QtySize
242.06
333.06
445.08
5173.09
644.04
768.11
859.02
943201.07
10201.5833
chunu
Cell Formulas
RangeFormula
A9A9=SUM(A2:A8)
B9B9=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)
B10B10=INT(SUMPRODUCT(A2:A8*B2:B8))+MOD(SUMPRODUCT(A2:A8*B2:B8),1)*100/12

#### Peter_SSs

##### MrExcel MVP, Moderator
Account details updated
Thanks. There may be some more shortening possible but with my limited testing this seems to produce the same results.

Excel Formula:
``````=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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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
ABCDE
1QtySize
242.06
333.06
445.08
523.09
644.04
768.11
859.02
928156feet
10
1112.03
1212.05
1312.067.17feet
14
158feet
166feet14inches
177feet2inches
187 ft 2 in
2a
Cell Formulas
RangeFormula
A9A9=SUM(A2:A8)
D9D9=SUMPRODUCT(A2:A8,INT(B2:B8))+CEILING(SUMPRODUCT(MOD(B2:B8,1)*100/12),1)
D13D13=SUMPRODUCT(A11:A13,INT(B11:B13))+SUMPRODUCT(MOD(B11:B13,1)*100/12)
D16D16=SUMPRODUCT(MOD(B11:B13,1)*100)
D17D17=MOD(SUMPRODUCT(MOD(B11:B13,1)*100),12)
B15B15=SUMPRODUCT(A11:A13,INT(B11:B13))+CEILING(SUMPRODUCT(MOD(B11:B13,1)*100/12),1)
B16B16=SUMPRODUCT(A11:A13,INT(B11:B13))
B17B17=SUMPRODUCT(A11:A13,INT(B11:B13))+INT(SUMPRODUCT(MOD(B11:B13,1)*100)/12)
B18B18=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

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

• chunu

Replies
1
Views
128
Replies
4
Views
130
Replies
4
Views
174
Replies
1
Views
126
Replies
13
Views
283

### Forum statistics

1,191,121
Messages
5,984,764
Members
439,909
Latest member
daigoku ### 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.

### Which adblocker are you using?    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

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