Summation in Excel

[sbryant]

I'm having a devil of a time figuring out how to do this in Excel. I am trying to put in a formula to calculate a summation where f(x)=.85^k for 1 to k, where k will be a positive integer pulled in from a cell within the spreadsheet.

I can manually create a cell for each iteration (k=1,k=2,......), but can't figure out how to use the equation editor to perform the calculation in one step. If I wanted to know the summation when k=1,000, obviously I can't (realistically) create enough cells to calculate that. I found an online calculator here: http://www.math.sc.edu/cgi-bin/sumcgi/calculator.pl that returns an answer in one step, but I want to have multiple "k"s so that I can graph the progression at whatever values I choose, without having a seperate cell for each value of k. For the 1,000 example, you'd enter sigma[1,1000,.85^k] in the box, which returns a value of 5.66 (repeating).

Is there a function in Excel (or an add-in) that will do this?

Thanks

Shane

[IcemanND]

i believe you are looking for the seriessum function.

[sbryant]

I explored seriessum this morning, and I don't think it will do what I am looking for. As I understand it, seriessum requires a hard number or cell references for the coefficients. What I want to be able to do is put in a formula, and the cell return the value (in isolation) without having to reference other cells.

I'm not doing a very good job of describing what I'm trying to do

Using Sigma notation from Algebra, the problem would have a "k=1" on the underside of the Sigma symbol, 10 (for example) above the sigma, then to the right of the sigma there would be a formula like .85^k. So the answer for this example would be .85^1 + .85^2 + .85^3 + .85^4 + .85^5 + .85^6 + .85^7 + .85^8 + .85^9 + .85^10 = 4.551. What I currently have done in excel is created seperate cells for .85^1, .85^2, .85^3, etc, and add in all the previous values to get a sum for a given value of k (which requires 10 cells in this example).

The problem is I want to do, for example, k=1000. To do that, I would have to continue the series above from .85^1 all the way to .85^1000 and get the sum, which would require 1000 cells (or a really long formula within one cell lol). I want to be able to put in the formula for k=1000, and the formula return the total value in isolation without having to total up a bunch of cells (similar to what the website does that I referred to in my previous post). Hopefully that makes sense :/

Maybe I'm not using seriessum correctly?

Thanks a bunch,

Shane

Edited July 6, 2007 by sbryant

[IcemanND]

looking at it again it's close but only kinda solves your problem.

seriessum(B1,B2,B3,B4:b6400)=4.551045

where:

b1=.85

b2=1

b3=1

b4:b6400 contains ten cells each with a value of 1.

Would have been better if the last value was the end value of the equation rather than a range for contaning the step value. So you would have to create a range that contain the correct number of ones for the number of required iterations.

[spacesurfer]

Dude, your series is not an arithmetic series. It's a geometric series of the type with sum:

For you, a = 0.85 and r = 0.85. If you want a sum for the first 10, then n = 9. If you want to sum 25, then n = 24.

Thus, you only need 1 formula.

=(0.85*(1-0.85^(A1-1))/(1-0.85))

where n = k - 1.

Make sure you know how to fix n and k.

Edited July 6, 2007 by spacesurfer

[IcemanND]

This was too long ago my brain is starting to hurt trying to dig through 20 years of other stuff to find this math info buried.

close but not quite:

(0.85*(1-0.85^(A1-1))/(1-0.85))=4.354170638 where a1=10, should be 4.551045042

0.85*(1-0.85~A1)/(1-0.85) does equal 4.551045042 when a1=10

or to make it easier since in this case a=r

(0.85-0.85^(A1+1)/(1-0.85)

[sbryant]

That works great. Thanks for the help

Shane