Trunc Multiply columns and Sum

C

chunu

Board Regular
Joined
Jul 5, 2012
Messages
96
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
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.
Sort by date Sort by votes
Peter_SSs

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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’)
 
Upvote 0
B

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,124
Office Version
  1. 2016
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
 
Upvote 0
Peter_SSs

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
bebo021999 said:
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
Click to expand...

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
 
Upvote 0
Peter_SSs

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
chunu said:
Account details updated
Click to expand...
Thanks. (y)

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
 
Upvote 0
D

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,750
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 
Upvote 0
C

chunu

Board Regular
Joined
Jul 5, 2012
Messages
96
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
Dave Patton said:
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?
Click to expand...
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
 
Upvote 0
D

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,750
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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"
 
Upvote 0
D

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,750
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=DOLLARFR(SUMPRODUCT(A11:A13,INT(B11:B13))+SUMPRODUCT(MOD(B11:B13,1)*100/12),12) yields 7.02
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Calculating hours in a shift into Reg, PM and Night
Replies
1
Views
128
saraapple
S
P
  • Question
Problem with IF Formula
Replies
4
Views
130
Fluff
Fluff
S
  • Solved
How to combine an if and or function
Replies
4
Views
174
Fluff
Fluff
SFCChase
  • Question
Averaging Issues
Replies
1
Views
126
mmhill
M
S
  • Solved
Deduct month to number
2
Replies
13
Views
283
Tom.Jones
T

Forum statistics

Threads
1,191,121
Messages
5,984,764
Members
439,909
Latest member
daigoku

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top