Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum product of many pairs of columns
Excel 2002.
I have already implemented 2 great solutions from this forum in my project, but am going round in circles on the final element. A macro pulls several ranges of information from a named worksheet in each file in a specified folder, and deposits it in columns in a new workbook. In the consolidation worksheet:- Cell K1 is account name. Cell K2 is account number Cell K3 is "Bal" Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19 Cell L3 is "Rate" Cell L4:L33 contains the daily interest rate e.g. 0.0575 This is repeated numerous times, so M1:N33 relates to a different account and so on. The daily balance and rate potentially are unique for each account and change daily. In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)), which sums all the negative balances for 1 April, and B4 does the same for positive balances. In C4 and D4 I want to show the sum of the interest products on negative and positive balances respectively, for 1 April. This can be crudely expressed as:- =SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0)) I've seen a few ideas that come close to doing this elegantly, for example using combinations of sumproduct, mod, column, offset and others I can't recall, but can't figure it out. Help from the experts will be hugely appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum product of many pairs of columns
=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:
$GH4/365),2) and =ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4: $GH4/365),2) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "KeenKiwi" wrote in message ... Excel 2002. I have already implemented 2 great solutions from this forum in my project, but am going round in circles on the final element. A macro pulls several ranges of information from a named worksheet in each file in a specified folder, and deposits it in columns in a new workbook. In the consolidation worksheet:- Cell K1 is account name. Cell K2 is account number Cell K3 is "Bal" Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19 Cell L3 is "Rate" Cell L4:L33 contains the daily interest rate e.g. 0.0575 This is repeated numerous times, so M1:N33 relates to a different account and so on. The daily balance and rate potentially are unique for each account and change daily. In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)), which sums all the negative balances for 1 April, and B4 does the same for positive balances. In C4 and D4 I want to show the sum of the interest products on negative and positive balances respectively, for 1 April. This can be crudely expressed as:- =SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0)) I've seen a few ideas that come close to doing this elegantly, for example using combinations of sumproduct, mod, column, offset and others I can't recall, but can't figure it out. Help from the experts will be hugely appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum product of many pairs of columns
Mate, that's awesome in the true sense of the word. Thanks so much for your
help Bob. At least I was on the right lines, but still so far away. "Bob Phillips" wrote: =ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4: $GH4/365),2) and =ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4: $GH4/365),2) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "KeenKiwi" wrote in message ... Excel 2002. I have already implemented 2 great solutions from this forum in my project, but am going round in circles on the final element. A macro pulls several ranges of information from a named worksheet in each file in a specified folder, and deposits it in columns in a new workbook. In the consolidation worksheet:- Cell K1 is account name. Cell K2 is account number Cell K3 is "Bal" Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19 Cell L3 is "Rate" Cell L4:L33 contains the daily interest rate e.g. 0.0575 This is repeated numerous times, so M1:N33 relates to a different account and so on. The daily balance and rate potentially are unique for each account and change daily. In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)), which sums all the negative balances for 1 April, and B4 does the same for positive balances. In C4 and D4 I want to show the sum of the interest products on negative and positive balances respectively, for 1 April. This can be crudely expressed as:- =SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0)) I've seen a few ideas that come close to doing this elegantly, for example using combinations of sumproduct, mod, column, offset and others I can't recall, but can't figure it out. Help from the experts will be hugely appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum product of many pairs of columns
Always happy to help a Kiwi <g
Regards Bob "KeenKiwi" wrote in message ... Mate, that's awesome in the true sense of the word. Thanks so much for your help Bob. At least I was on the right lines, but still so far away. "Bob Phillips" wrote: =ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4: $GH4/365),2) and =ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4: $GH4/365),2) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "KeenKiwi" wrote in message ... Excel 2002. I have already implemented 2 great solutions from this forum in my project, but am going round in circles on the final element. A macro pulls several ranges of information from a named worksheet in each file in a specified folder, and deposits it in columns in a new workbook. In the consolidation worksheet:- Cell K1 is account name. Cell K2 is account number Cell K3 is "Bal" Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19 Cell L3 is "Rate" Cell L4:L33 contains the daily interest rate e.g. 0.0575 This is repeated numerous times, so M1:N33 relates to a different account and so on. The daily balance and rate potentially are unique for each account and change daily. In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)), which sums all the negative balances for 1 April, and B4 does the same for positive balances. In C4 and D4 I want to show the sum of the interest products on negative and positive balances respectively, for 1 April. This can be crudely expressed as:- =SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0)) I've seen a few ideas that come close to doing this elegantly, for example using combinations of sumproduct, mod, column, offset and others I can't recall, but can't figure it out. Help from the experts will be hugely appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding columns w/new formulas BUT keeping subtotals | Excel Discussion (Misc queries) | |||
How do I wrap Excel columns in a printout? | Excel Discussion (Misc queries) | |||
filtering unique in multiple columns | Excel Worksheet Functions | |||
filter on pivot table | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |