Reverse mutiplication

[themev]

I am trying to build a model that includes depleting assets.

The rates are in cells C3:I3 representing Year 1 to Year 7

1.0%

2.0%

5.0%

10.0%

15.0%

20.0%

20.0%

I have annual values in cells C12:I12 with I12 representing year 7.

36,211

49,037

55,356

42,809

72,941

90,768

119,528

So in year 7, the formula I would want is (119,528 * 1%) + (90,768 * 2%) + (72,941 * 5%) + (42,809 * 10%) + (55,356 * 15%) + (49,037 * 20%) + (36,211 * 20%). I can enter it manually, but I was wondering if there are some formulas I could use to make this easier and to also be able to extend the values beyond year 7, but still have it multiply by the rates in C3:I3.

Any help would be appreciated.

 

[BenMcBen]

Array formulas!

=SUM(C3:I3*C12:I12)

enter with CTRL+SHIFT+ENTER

 

[KRice]

To flip the second list, consider this:

Book20200515.xlsx
	A	B	C	D
1	1	2	3	4
2	1	2	3	4
3
4	20
Sheet13

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(A1:D1,INDEX(A2:D2,N(IF({1},MAX(COLUMN(A2:D2))-COLUMN(A2:D2)+1))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

I was curious about whether this could be done without an array formula. Here is another option that doesn't use an array formula in A5.

Book20200515.xlsx
	A	B	C	D	E
1	1	2	3	4	5
2	1	2	3	4	5
3
4	35	array formula
5	35	non-array formula
Sheet13

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(A1:E1,INDEX(A2:E2,N(IF({1},MAX(COLUMN(A2:E2))-COLUMN(A2:E2)+1))))
A5	A5	=SUMPRODUCT(A1:E1,MMULT(A2:E2,(ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(A2:E2)))=(COLUMN(INDEX(1:1,COLUMNS(A2:E2)))-COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(A2:E2)))+1))+0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[joeu2004]

Normally-entered (just press Enter as usual):

=SUMPRODUCT(C3:I3, C12:I12)

 

[Eric W]

Another non-CSE formula, using KRice's layout:

=SUMPRODUCT(A1:E1,SUBTOTAL(9,OFFSET(A2,0,COLUMN($E2)-COLUMN($A2:$E2))))

Although I thought the MMULT formula was pretty clever.

 

[themev]

Thanks everybody!

 

[KRice]

Here is a summary of the options that reverse the order of the 2nd array adjusted for the ranges of interest. I like Eric's solution the best, shown in A17...thanks Eric!

Book20200515.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3			1	2	3	4	5	6	7
4
5
6
7
8
9
10
11
12			1	2	3	4	5	6	7
13
14	84	array formula
15	84	non-array formula		84	<--CHECK
16
17	84	Eric W's formula
Sheet13

Cell Formulas
Range	Formula
A14	A14	=SUMPRODUCT(C3:I3,INDEX(C12:I12,N(IF({1},MAX(COLUMN(C12:I12))-COLUMN(C12:I12)+1))))
A15	A15	=SUMPRODUCT(C3:I3,MMULT(C12:I12,(ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(C12:I12)))=(COLUMN(INDEX(1:1,COLUMNS(C12:I12)))-COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(C12:I12)))+1))+0))
D15	D15	=C3*I12+D3*H12+E3*G12+F3*F12+G3*E12+H3*D12+I3*C12
A17	A17	=SUMPRODUCT(C3:I3,SUBTOTAL(9,OFFSET(C12,0,COLUMN($I12)-COLUMN($C12:$I12))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

One last option (hopefully!): This version will adapt if you add more columns to your table:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
2	Rate	1%	2%	5%	10%	15%	20%	20%
3	Value	36211	49037	55356	42809	72941	90768	119528
4
5	Product	36291.59
6	column-changing Product	36291.59
Sheet24

Cell Formulas
Range	Formula
B5	B5	=SUMPRODUCT(B2:H2,SUBTOTAL(9,OFFSET(B3,0,COLUMN(H3)-COLUMN($B3:$H3))))
B6	B6	=SUMPRODUCT(B2:K2,IFERROR(SUBTOTAL(9,OFFSET(A3,0,LOOKUP(9^99,B3:K3,COLUMN(B3:K3))-COLUMN($B3:$K3)+1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

If you add data in I2:I3, the B6 formula will handle it without needing to change the ranges, as long as the initial ranges encompass the maximum extent of the table. But it does require the CSE entry.