program formular in Excel
 

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

program formular in Excel
 

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

In article , "Manfred"
wrote:

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

program formular in Excel
 

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


"Manfred" wrote in message
...
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

program formular in Excel
 

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.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Manfred" wrote in message ...
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

program formular in Excel
 

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

In article , says...
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

program formular in Excel
 

"Dana DeLouis" wrote...
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
...

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

--
To top-post is human, to bottom-post and snip is sublime.

program formular in Excel
 

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))

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Harlan Grove" wrote in message
...
"Dana DeLouis" wrote...
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
..

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

--
To top-post is human, to bottom-post and snip is sublime.

program formular in Excel
 

"Dana DeLouis" wrote...
. . . I think bringing the d^n inside the pv function
might work also.

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

Touché!