Hi all,
I have the following formula:
=SUMPRODUCT($C$3:C7,INDIRECT("R"&ROW($D$3)+ROW(D7)-ROW($D$3:D7)&"C"&COLUMN($D$3),FALSE))
Which is designed to take a series of numbers as follows
0.4 0.2
0.3 0.2
0.2 0.2
0.1 0.2
0.0 0.2
in columns C and D, and sumproduct the first set of numbers with the reverse of the second set of numbers. ignore the actual numbers for the moment as this problem is going to be generalized.
When I evaluate the output of the INDIRECT function using F9 I get {0.2;0.2;0.2;0.2;0.2} and substituting this array constant in place of the formula that gave me the array constant gives me the correct result.
Why is it that I can't use an array formula within another array formula, when evaluating the inner array formula gives me the exact component needed to make the outer array formula work? How can I fix this?
Regards,
Alex
I have the following formula:
=SUMPRODUCT($C$3:C7,INDIRECT("R"&ROW($D$3)+ROW(D7)-ROW($D$3:D7)&"C"&COLUMN($D$3),FALSE))
Which is designed to take a series of numbers as follows
0.4 0.2
0.3 0.2
0.2 0.2
0.1 0.2
0.0 0.2
in columns C and D, and sumproduct the first set of numbers with the reverse of the second set of numbers. ignore the actual numbers for the moment as this problem is going to be generalized.
When I evaluate the output of the INDIRECT function using F9 I get {0.2;0.2;0.2;0.2;0.2} and substituting this array constant in place of the formula that gave me the array constant gives me the correct result.
Why is it that I can't use an array formula within another array formula, when evaluating the inner array formula gives me the exact component needed to make the outer array formula work? How can I fix this?
Regards,
Alex