Sigma Summation HELP!

[hern]

I'm trying to do a summation, but not the simple kind you may think of where it's an autosum. I'd like to do a math summation (sigma) where i=0 and it runs for n times.

E=sigma

Example: E from j=0 to n=10 ( n!/(j!*n!))

How do you sum that formula the 11 runs needed where it fills in n and j for you???

Any help would GREATLY be appreciated.

 

[pgc01]

Hi hern

Can you please clarify what j and n are?

I'm trying to understand what you mean by "E from j=0 to n=10". I believe it's incorrect.

In a sigma each index has a low limit and a high limit.

You cannot fill in an index value since it varies. What you can, and you must do, is to fill in the low and the high limits of each index.

The syntax for a sigma with one index would be

E (from j=low to high ) function(j)

And you must
- fill in the values low and high
- supply the function(j)

Can you please clarify and give an example solved manually?

 

[tusharm]

As has already been pointed out to you, the limits for your summation are inconsistent.

Further, the function you used in your example can be simplified to 1/j! If you want to sum that from j=0 to j=10, you can do the following. This is a capability of an array formula that is not something many people leverage.

Use the array formula(*) =SUM(1/FACT(ROW(INDIRECT("1:11"))-1))

INDIRECT("1:11") creates a reference to the 1st 11 rows.
ROW() creates a vector 1,2,3,...11
-1 yields 0,1,2,...,10
FACT() yields 0!, 1!, 2!,..., 10!
Of course, the above relies on the fact that the factorial function in XL is array-aware. Not all functions are.
SUM(1/...) does the rest of the work.

Of course, one can also do the above the long way.
In A1 enter =FACT(ROW()-1). Copy all the way down to A11.
In C1 enter =1/A1. Copy all the way down to C11.
In D1 enter =SUM(C1:C11).
Since an array formula is much more of a "black box" approach, it is always a good idea to verify it. [Keep in mind that it is effectively like writing a piece of code and one would always test code. The same applies to an array formula.]

(*) An array formula is entered not with the ENTER key but with the combination SHIFT+CTRL+ENTER.

I'm trying to do a summation, but not the simple kind you may think of where it's an autosum. I'd like to do a math summation (sigma) where i=0 and it runs for n times.

E=sigma

Example: E from j=0 to n=10 ( n!/(j!*n!))

How do you sum that formula the 11 runs needed where it fills in n and j for you???

Any help would GREATLY be appreciated.

 

[pgc01]

Hi again

I'm going away for a long weekend but I would like to leave some information so that you understand better how to do a sigma.

Since I don't know what's your level in working with arrays in formulas I'll go slowly. I'm sorry if it's too slow for you.

Your formula must be mistyped because ( n!/(j!*n!)) is just ( 1/j!). However we can use this just as an example



Let's say we want to calculate

S (j=3 to 6) (1/j!) = 1/3! + 1/4! + 1/5! +1/6! = 0.218055556

We must use an array formula and get a counter. We can use row(3:6) to get the array {3,4,5,6}

in this case if we hardcode we have

=SUMPRODUCT(1/FACT(ROW(3:6)))

you can check the result.



You want, however, to supply variable low and high limits for the sigma.

S (j=low to high) (1/j!)

Let's say that low is in B1 and high in B2.

We will build the counter using INDIRECT and we get the formula

=SUMPRODUCT(1/FACT(-1+ROW(INDIRECT(B1+1&":"&B2+1))))

Now if you enter 3 in B1 and 6 in B2 you get the same result as before.

Notes:
Row(low:high) anly accepts positive integers from 1 to 65536. That's why I add and subtract 1, to allow you to enter any positive integer or zero. If you want to allow for negative numbers for low and high you'll have to adjust the formula.

Not all functions accept arrays as parameters. Check the help.


I hope that it will help you solving your problem.
Have a good weekend.