在Excel中求和? [英] Summation in excel?

问题描述

我需要取(a ^ n * b ^(n-i))从i = 1到n的总和，其中a，b为常数.n的值范围是1到24，代表贷款已经放出并且现在还清的月份.

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.

例如，在我尝试过的n = 1的第一行中

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))

其中B5和B3是常数a和b，而A10:A33是n = 1，...，24的值.无论如何，此求和公式对于n = 1很好，但对于其余部分在表格中，公式无法正确计算总和.我该如何解决?

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?

推荐答案

首先:此类公式必须是数组公式.但是，即使作为数组公式，您的公式似乎也不是从(a ^ n * b ^(ni))的i = 1至n的总和"的精确Excel公式，因为在我看来，$ A $ 10:A10等于我而不是n.

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.

所以

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

我认为

是正确的Excel公式.

is the right Excel formula in my opinion.

要创建数组公式，请将公式放入不带大括号的单元格中.然后，按[Ctrl] + [Shift] + [Enter]，而不是单独按[Enter].然后大括号应该会自动出现.

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.

这篇关于在Excel中求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！