Sigma operation in Excel 2007

[javadjavad]

How can I use sigma with boundaries in excel 2007? For example I want to calculate this value:

sigma [e^(m-n)*i(m)] in which n values are a column starting from 0 to 100 and for each n, m is started from 0 ended to n and i(m) is corresponding value specified in Raw m and form a column.for example for n=100:

sigma [e^(m-100)*i(m)] and m is 0 to 100.Please don't tell to write for each raw separately and then sum up because for n=1 to 100 I should do this operation for each n, n times which leads to 1+2+3+...+100=100*101/2=5050 times.

 

[Fenrirshowl]

Would the SERIESSUM function work? (Analysis toolpack add in required)?

=SERIESSUM(EXP(1),n,-1,C1:C100)

Where C1:C100 is your range of values / i(m) values.

(If fact you could probably use =SERIESSUM(EXP(1),COUNT(C1:C100),-1,C1:C100) to save having to define the value of n??)

Fen

 

[lionelhill]

OOPS Numerical alarm going off. I'm not sure that what you are doing makes numerical sense, and Excel is not the tool to warn you.

These values are going to span an absolutely enormous range. e^(1-100) is very small compared to (e^(99-100). Excel's internal number format can't possibly add one to the other because it doesn't have anything like enough decimals. But if you add a very small number repeatedly to a much bigger one, you might, eventually, add enough that it ought to show up in the smaller decimals.

Without knowing in what order seriessum or any other internal approach is going to go about summing the series, you've got to be awfully careful about this sort of calculation.