Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
color code series by an independent column of data | Charts and Charting in Excel | |||
Jon Peltier's code: can't delete initial series?? | Charts and Charting in Excel | |||
series graph -- one series being added to another series | Charts and Charting in Excel | |||
Series Code | Excel Programming |