Summation in excel?

Question

I need to take the sum from i=1 to n of (a^n * b^(n-i)) where a,b are given constants. The value for n ranges from 1 to 24, representative of the months since a loan has been made and is now being paid off.

For example, in the first row where n = 1 I've tried

=SUM(POWER($B$5, $A$10:A10)*POWER($B$3, A10 - $A$10:A10))

where B5 and B3 are the constants a and b and A10:A33 are values for n = 1, ..., 24. In any case, this formula for my sum works fine for n=1, but for the rest of the table, the formula isn't calculating the sum correctly. How can I fix this?

Answer 1

This array formula (entered with Shift Ctrl Enter) works:

=$B$1^$B$3*SUM($B$2^($B$3-ROW(INDIRECT("1:"&$B$3))))

assuming this layout (adjust to suit your layout):

Note that I have taken the factor a*n out of the summation since it is a constant.

Also note how I hijack the ROW() function to generate a sequence of numbers. In an array formula, ROW(m:n) returns an array of integers from m to n; but you have to use INDIRECT to hard-code those numbers, otherwise they will change as you add or delete rows. So in the example above, ROW(INDIRECT("1:"&$B$3)) returns {1;2;3;4}. Reference for this trick.

Answer 2

First: Such formulas have to be array formulas. But your formula seems not to be the exact Excel formula to "sum from i=1 to n of (a^n * b^(n-i))" even as array formula because in my opinion $A$10:A10 is the equivalent to i and not n.

So

{=SUM($B$3^$A10*$B$5^($A10-$A$10:$A10))}

is the right Excel formula in my opinion.

To create a array formula put the formula into the cell without the curly brackets. Then press [Ctrl]+[Shift]+[Enter] instead of [Enter] alone. Then the curly brackets should appear automatically.