Summation over a range

[Guest]

Does anyone know of an easy way to sum a function over a range of values such
as from k=0 to k=N, where N is a variable in a cell?

Thanks,

 

[Peo Sjoblom]

=SUM(C1:INDEX(C:C,B1))


where B1 holds the variable and C is the column you want to sum based on the
variable


--


Regards,


Peo Sjoblom

 

[David Hilberg]

If you want to do it all in one cell, you can hack the row function
into a counter:

Row(1:10)
Row(Indirect("1:" & A1))

E.g., to sum the first 10 terms of the harmonic series, with 10 in A1:

=SUM(1/ROW(INDIRECT("1:" & A1)))

array-entered with Ctrl+Shift+Enter, so that Excel bounds the formula
with braces { }, otherwise only the first value in Row will be used.

Note that you cannot use 0 in this counter, as Excel has no 0th row.

- David