Sigma function in excel

Tag: excel Author: cs7825678 Date: 2009-07-13

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.

Should this be moved to superuser?
@Nick: Excel formulas may be a familiar world to Morts, but it's still code. OP is not asking how to change his screen saver. ;)

Other Answer1

@javad, your description is very difficult to follow e.g. "i(m) is corresponding value specified in Raw m and form a column"???

My guess: You want to tabulate the values of function F(n) for n=0,...,100. F(n) is defined as the sum over m=0,...,n of the expression e^(m-n)*i(m) -- where i(m) is some function of m. Abbreviate this as F(n) = sigma(0,n) of e^(m-n)*i(m)

Is that correct? Whether it's correct or not, please edit your question to provide a clear unambiguous description of what you want.

You should also manually calculate the first few values (say F(0) to F(3)) and publish those as well as i(0) to i(3) for use as test data.

You might also give an idea of what kind of precision you expect.

Here's a tentative start on a solution:

Firstly rewrite F(n) as (sigma(0,n) of e^m * i(m)) / e^n
Then fill in the cells like this:

a1: heading n, a2 to a5: 0,1,2,3  
b1: heading i(n), b2 to b5: i(0), ..., i(3)  
c1: heading e^n, c2: =exp(a2) and copy down  
d1: heading i(n)*e^n, d2: =b2*c2 and copy down  
e1: heading accum, e2: =d2, e3: =e2+d3 and copy down  
f1: heading F1(n), f2: =e2/c2 and copy down  
g1: heading F2(n), g2: =sum(d$2:d2)/c2 and copy down

F1(n) and F2(n) ate two slightly different ways of calculating your F(n). F2 looks very much like what you say you don't want ("Please don't tell to write for each raw separately and then sum up") -- you may like to explain why you think you don't want that, because (1) 5000 calculations is a trivially small number and (2) I've filled out the above table to n=100 and the recalculation time is not noticeable. You'll notice that the F2(n) doesn't use the clunky "accum" (running total) column.

Other Answer2

Can you use SERIESSUM() to accomplish this?

Other Answer3

For one-dimensional sums of series, you can use the Excel function

SUMPRODUCT( yourformulahere( ROW(1:100) ))

to generate the sigma from 1 to 100 of your arbitrary f(n). ROW(1:100) expands to the series {1,2,...100}.

It sounds like you are doing a double summation here, though, with the inner dependent on the outer. This is left as an exercise for the reader. HTH.