Summing 'n' values in a spreadsheet

[dziw]

i am trying to enter an equation into excel which will sum 1 to n values calculated from an equation, at maximum 'n' will have a value of around 100. the basis of the equation looks something like this; sum between 0 and n of (2/(1.5^n))does anybody know how to program this into excel so that I can enter a value of n and excel will work it out?!

many thanks in advance!!

[davesexcel]

Maybe you should look at goal seek

Have your formulas created and then play with goalseek by hitting tools and selecting goalseek

Then of course there is Solver, brrrr....

[Carim]

Hi,

If I understand your request, with two assumptions:
data to be added is in colum A
input of n goes into cell C1
then cell B1 formula would be :

=SUM(A1:OFFSET(A1,2/(1.5^C1),0))

HTH
Carim

[dziw]

thanks for your advice guys, still not quite sure if its quite what im looking for, not sure i explained the equation properly! i'll write it exactly as it appears (the letters in it are just in the place of other words in my worksheet) it would be written out long hand as; sum (1 to n) of [ ((A/B)/(1+0.0475)^1)+((A/B)/(1+0.0475)^2)+((A/B)/(1+0.0475)^3)+.........((A/B)/(1+0.0475)^n) ]

hope this makes more sense!
thanks again!

[Carim]

Sorry I totally misunderstood your question ...

You are after solving an equation ...

Indeed, you should look at Tools Solver ...

HTH
Carim

[dziw]

I take it from peoples replies that Tools Solver is not a good thing...!

[MrShorty]

Solver is a good thing for the right problem. Not sure this is the right problem for Solver.

A couple of possible approaches, depending on exactly what you need.

1) Use a lookup table. In another worksheet, build the lookup table:

A----B----C
1---=1.0475^(-a1)---=sum($a$1:a1)
=a1+1---=1.0475^(-a2)---=sum($a$1:a2) copied to row 100

In the original worksheet enter =A/B*VLOOKUP(n,sheet2!$A$1:$C$100,3) to get the desired sum.

2) For greater flexibility, use a VBA UDF.

Function mycrazysum(A as double, B as double, C as double, n as long) as double
temp=0
For i=1 to n
temp=temp+C^-i
next i
mycrazysum=A/B*temp
end function

You can then call the function from a worksheet cell like you would built in functions =mycrazysum(A,B,C,n)

I'm sure there are other possibilities, but that should give you some place to start.