Need code to sum a series

[davidm]

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

[duane]

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

[davidm]

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


David

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[William Benson]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[William Benson]

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[Dana DeLouis]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[[email protected]]

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