Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct while number of added fields is changing?
I need to calculate how big amount of interest income is amortizing. I have
sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so on period after sales, and the Yield applied to each period sales. The problem is, that I could not figure out, how to build the sumproduct function so, that on each next period it would take in account, that there is new volume that started to amortize and the others have one more amortization rate to be taken in account. B C D E F 3 New Sales 195 192 146 394 97 596 1 076 343 4 New Sales Yield 7,25% 7,22% 7,19% 7,26% 5 Amortization 1,12% 1,13% 1,13% 1,14% 6 Amortized interest Income 159 437 795 2 031 In simple formulas: C6=$B3*$B4*SUM($B5:B5) D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5) E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5) and so on Can somebody help please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct while number of added fields is changing?
I tried to avoid using auxiliary cells but either it is impossible or
I am missing something. The aux values you need are the numbers 1, 2, 3 growing in parallel to B3, C3, D3, .... I assume you have them in row 2, starting from B2. You can enter the following formula in C6 and copy to the right: =SUMPRODUCT($B3:B3*$B4:B4*(SUBTOTAL(9,OFFSET($B5,0 ,0,1,C2-$B$2:B2)))) Maybe one of the posters will jump in and suggest a solution using COLUMN or ROW to generate the needed arrays w/o using the aux cells. HTH Kostis Vezerides On Nov 26, 5:33*pm, Massimus wrote: I need to calculate how big amount of interest income is amortizing. I have sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so on period after sales, and the Yield applied to each period sales. The problem is, that I could not figure out, how to build the sumproduct function so, that on each next period it would take in account, that there is new volume that started to amortize and the others have one more amortization rate to be taken in account. * * * * B * * * C * * * D * * * E * * * F 3 * * * New Sales * * * 195 192 146 394 97 596 *1 076 343 4 * * * New Sales Yield 7,25% * 7,22% * 7,19% * 7,26% 5 * * * Amortization * *1,12% * 1,13% * 1,13% * 1,14% 6 * * * Amortized interest Income * * * 159 * * 437 * * 795 * * 2 031 In simple formulas: C6=$B3*$B4*SUM($B5:B5) * D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5) * * * E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5) and so on Can somebody help please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct while number of added fields is changing?
Huge thanks!
It works perfectly, I hope I can replace the auxiliary stuff with column function combination, and even if I cannot, it is still great. "vezerid" wrote: I tried to avoid using auxiliary cells but either it is impossible or I am missing something. The aux values you need are the numbers 1, 2, 3 growing in parallel to B3, C3, D3, .... I assume you have them in row 2, starting from B2. You can enter the following formula in C6 and copy to the right: =SUMPRODUCT($B3:B3*$B4:B4*(SUBTOTAL(9,OFFSET($B5,0 ,0,1,C2-$B$2:B2)))) Maybe one of the posters will jump in and suggest a solution using COLUMN or ROW to generate the needed arrays w/o using the aux cells. HTH Kostis Vezerides On Nov 26, 5:33 pm, Massimus wrote: I need to calculate how big amount of interest income is amortizing. I have sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so on period after sales, and the Yield applied to each period sales. The problem is, that I could not figure out, how to build the sumproduct function so, that on each next period it would take in account, that there is new volume that started to amortize and the others have one more amortization rate to be taken in account. B C D E F 3 New Sales 195 192 146 394 97 596 1 076 343 4 New Sales Yield 7,25% 7,22% 7,19% 7,26% 5 Amortization 1,12% 1,13% 1,13% 1,14% 6 Amortized interest Income 159 437 795 2 031 In simple formulas: C6=$B3*$B4*SUM($B5:B5) D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5) E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5) and so on Can somebody help please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
? avoid changing sum function as rows added? | New Users to Excel | |||
number of fields in the row fields in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table problem, blank fields aren't being added | Excel Discussion (Misc queries) | |||
How do I keep a formula from changing if a row is added or deleted | Excel Discussion (Misc queries) | |||
How do I keep a formula from changing if a row is added or delete. | Excel Discussion (Misc queries) |