program formular in Excel

[Manfred]

Hi

how can I program that formular in Excel:

sum i = 1 to n from d^(i-1)*z^(n-i+1)

or have anybody an idea to describe that with a function f(n,d,z)?

Thanks in advance
Manfred

 

[JE McGimpsey]

One way:

Public Function foo(n As Double, d As Double, z As Double) As Double
Dim i As Long
For i = 1 To n
foo = foo + d ^ (i - 1) * z ^ (n - i + 1)
Next i
End Function

If you're not familiar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Chip Pearson]

Manfred,

Try something like

Function DoSum(N As Double, D As Double, Z As Double) As Double
Dim Res As Double
Dim I As Long
For I = 1 To N
Res = Res + D^(I-1)*Z^(N-I+1)
Next I
DoSum = Res
End Function

You can then call this from a worksheet cell with a formula like
=DOSUM(N,D,Z)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dana DeLouis]

or have anybody an idea to describe that with a function f(n,d,z)?

Public Function Fx(n As Double, d As Double, z As Double) As Double
Fx = (((d / z) ^ n - 1) * z ^ (1 + n)) / (d - z)
End Function

HTH.

 

[Tushar Mehta]

For a non-VBA solution, not that it is necessarily superior to a VBA
UDF...

Suppose you have three cells named d, z, and n, respectively. Further,
create a name (Insert | Name > Define...) IVals =ROW(INDIRECT("1:"&n))
Now, the array formula =SUM(d^(IVals-1)*z^(n-IVals+1)) will give you
the desired result.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

Public Function Fx(n As Double, d As Double, z As Double) As Double
Fx = (((d / z) ^ n - 1) * z ^ (1 + n)) / (d - z)
End Function

...

Well, if you're going to introduce math into the discussion, why bother with a
udf at all?

=d^n*PV(d/z-1,n,-1)

would suffice, though it should be wrapped in parentheses if used as an
expression in a larger formula,

=onething+(d^n*PV(d/z-1,n,-1))*andanother

 

[Dana DeLouis]

Thanks Harlan. Never even saw that one! Nice catch. :>)
Working with your idea, I think bringing the d^n inside the pv function
might work also.

=PV(d/z-1,n,-(d^n))

 

[Harlan Grove]

. . . I think bringing the d^n inside the pv function
might work also.

=PV(d/z-1,n,-(d^n))

....

Touché!