Need help with stack multiplication

[exceluser1609]

I have following data that varies by time:

The table below shows the base cashflows at each time

t	1	2	3	4	5	6	7	8	9	10	11	12
Cashflows	CF1	CF2	CF3	CF4	CF4	CF5	CF7	CF8	CF9	CF10	CF11	CF12

<tbody>
</tbody>

The table below shows the scaling factor that varies by time and are used to scale up the cashflows:

t	1	2	3	4	5	6
Scaling factor	SF1	SF2	SF3	SF4	SF4	SF5

<tbody>
</tbody>

Looking for a formula to do stack multiplication at each time, with the following required output

t	Output
1	SF1*CF1
2	SF1*CF2 + SF2*CF1
3	SF1*CF3 + SF2*CF2 + SF3*CF1
4	SF1*CF4 + SF2*CF3 + SF3*CF2 + SF4*CF1
5	SF1*CF5 + SF2*CF4 + SF3*CF3 + SF4*CF2 + SF5*CF1
6	SF1*CF6 + SF2*CF5 + SF3*CF4 + SF4*CF3 + SF5*CF2 + SF6*CF1
and so on	SF1*CFn + SF2*CF(n-1) +………………………………..+ SFn*CF1

<tbody>
</tbody>

Please let me know if there is any formula to do this quickly and if further details are required.

Thank you

 

[Eric W]

Welcome to the board.

Maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	t	1	2	3	4	5	6	7	8	9	10	11	12
3	Cashflows	1	2	3	4	5	6	7	8	9	10	11	12
4
5	t
6	Scaling Factor	1	2	3	4	5	6
7		1	1.1	1.2	1.3	1.4	1.5
8
9	t	Output
10	1	1
11	2	3.1
12	3	6.4
13	4	11
14	5	17
15	6	24.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B10 =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$7,0,ROW(INDIRECT("1:"&A10))-1))*SUBTOTAL(9,OFFSET($B$3,0,A10-ROW(INDIRECT("1:"&A10))))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[exceluser1609]

Welcome to the board.

Maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	t	1	2	3	4	5	6	7	8	9	10	11	12
3	Cashflows	1	2	3	4	5	6	7	8	9	10	11	12
4
5	t
6	Scaling Factor	1	2	3	4	5	6
7		1	1.1	1.2	1.3	1.4	1.5
8
9	t	Output
10	1	1
11	2	3.1
12	3	6.4
13	4	11
14	5	17
15	6	24.5

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B10 =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$7,0,ROW(INDIRECT("1:"&A10))-1))*SUBTOTAL(9,OFFSET($B$3,0,A10-ROW(INDIRECT("1:"&A10))))) <tbody> </tbody>

<tbody>
</tbody>

thank you so much.. very helpful

 

[exceluser1609]

thank you so much Eric, very helpful

 

[StephenCrump]

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$7,0,ROW(INDIRECT("1:"&A10))-1))*SUBTOTAL(9,OFFSET($B$3,0,A10-ROW(INDIRECT("1:"&A10)))))

... which given your helper column could be simplified to:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$7,0,A$10:A10-1))*SUBTOTAL(9,OFFSET($B$3,0,A10-A$10:A10)))

or even more succinctly:

=SUMPRODUCT(N(OFFSET(B$7,,A$10:A10-1)),N(OFFSET(B$3,,A10-A$10:A10)))