Need code to sum a series
 

I have been grappling with the algorithm to sum up the following series,
arguably a very awkward one.

1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.

It would appear something between a recursive and For/Next construct
might be required but I can't quite get my head around it. Here was my
attempt (using a Function) which failed with Run time "out of stack
space" error.

Function SumSeries(n)
For i = n to 1 step -1
s = s + 1/(i*i-1*Sumseries(i))
Next
SumSeries = s
End Function

Sub test()
Msgbox Sumseries(150)
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

this gave me a result of 1.718281711 in cell b1

note i output each denominator in column a

Sub series()
s = 0
For j = 1 To 150
denom = 1
For i = 1 To j
denom = denom * i
Next i
'write each denominator out
Cells(j, 1) = denom
s = s + 1 / denom
Next j
Cells(1, 2) = s
End Sub


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

Many thanks Duane. That was very nifty! I will adapt code to avoid
writing to worksheet range.


David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

In A1 put in the formula
=Fact(row())

then drag fill it down as far as you wish.

This is your denominator. In an adjacent column (example in B1 put in 1/A1
and drag fill down) You will see that it doesn't take too many rows until
any additional term will be below the precision of the machine to reflect
it. For me,

The 17th term and beyond returns

1.71828182845905

Your series is of the form Sum(1/N-factorial)

--
Regards,
Tom Ogilvy

"davidm" wrote in
message ...

I have been grappling with the algorithm to sum up the following series,
arguably a very awkward one.

1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.

It would appear something between a recursive and For/Next construct
might be required but I can't quite get my head around it. Here was my
attempt (using a Function) which failed with Run time "out of stack
space" error.

Function SumSeries(n)
For i = n to 1 step -1
s = s + 1/(i*i-1*Sumseries(i))
Next
SumSeries = s
End Function

Sub test()
Msgbox Sumseries(150)
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

You can have a user defined function I suppose, store it in Personal.XLS and
call it with
=Personal.XLS!CalcSeries entered as the cell formula

Function CalcSeries(WhereToStop As Long)
Dim i As Long
Dim RunningSum As Double
'1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the nth

For i = 1 To WhereToStop
RunningSum = RunningSum + 1 / WorksheetFunction.Fact(i)
Next i
CalcSeries = RunningSum

End Function

"davidm" wrote in
message ...

I have been grappling with the algorithm to sum up the following series,
arguably a very awkward one.

1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.

It would appear something between a recursive and For/Next construct
might be required but I can't quite get my head around it. Here was my
attempt (using a Function) which failed with Run time "out of stack
space" error.

Function SumSeries(n)
For i = n to 1 step -1
s = s + 1/(i*i-1*Sumseries(i))
Next
SumSeries = s
End Function

Sub test()
Msgbox Sumseries(150)
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

Or simpler yet:


Function CalcSeries(WhereToStop As Long) As Double
Dim i As Long
'1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.
For i = 1 To WhereToStop
CalcSeries = CalcSeries + 1 / WorksheetFunction.Fact(i)
Next i
End Function


"William Benson" wrote in message
...
You can have a user defined function I suppose, store it in Personal.XLS
and call it with
=Personal.XLS!CalcSeries entered as the cell formula

Function CalcSeries(WhereToStop As Long)
Dim i As Long
Dim RunningSum As Double
'1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the nth

For i = 1 To WhereToStop
RunningSum = RunningSum + 1 / WorksheetFunction.Fact(i)
Next i
CalcSeries = RunningSum

End Function

"davidm" wrote in
message ...

I have been grappling with the algorithm to sum up the following series,
arguably a very awkward one.

1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.

It would appear something between a recursive and For/Next construct
might be required but I can't quite get my head around it. Here was my
attempt (using a Function) which failed with Run time "out of stack
space" error.

Function SumSeries(n)
For i = n to 1 step -1
s = s + 1/(i*i-1*Sumseries(i))
Next
SumSeries = s
End Function

Sub test()
Msgbox Sumseries(150)
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

The 17th term and beyond returns
1.71828182845905

Hi. The limit of this Series is:
=EXP(1)-1

which also returns:
1.71828182845905

HTH
--
Dana DeLouis
Win XP & Office 2003


"Tom Ogilvy" wrote in message
...
In A1 put in the formula
=Fact(row())

then drag fill it down as far as you wish.

This is your denominator. In an adjacent column (example in B1 put in 1/A1
and drag fill down) You will see that it doesn't take too many rows until
any additional term will be below the precision of the machine to reflect
it. For me,

The 17th term and beyond returns

1.71828182845905

Your series is of the form Sum(1/N-factorial)

--
Regards,
Tom Ogilvy

"davidm" wrote in
message ...

I have been grappling with the algorithm to sum up the following series,
arguably a very awkward one.

1/(1*1) + 1/(1*2) + 1/(1*2*3)+1/(1*2*3*4) + ... + to the 150th term.

It would appear something between a recursive and For/Next construct
might be required but I can't quite get my head around it. Here was my
attempt (using a Function) which failed with Run time "out of stack
space" error.

Function SumSeries(n)
For i = n to 1 step -1
s = s + 1/(i*i-1*Sumseries(i))
Next
SumSeries = s
End Function

Sub test()
Msgbox Sumseries(150)
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=385467

Need code to sum a series
 

Do not really need a VBA UDF, this should work ona worksheet.

{=SUM(1/FACT(ROW(1:150)))}

or in VBA if thats what you really need

Function test(n As Integer)

Dim s As String
s = "=SUM(1/FACT(ROW(1:" + Trim(Str(n)) + ")))"
test = Evaluate(s)

End Function