Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Fibonacci function/command in excel?...tia sal
Greetings all, Is thier a Fibonacci function or command built into excel? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
No.
In article , wrote: Is thier a Fibonacci function or command built into excel? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
If you would like a non-looping custom function, this is good to n=73 (all
digits). Not sure if you need to go up to Excel vba's limit of about n=136 or so. Function Fibonacci(n As Long) '// Good to n=73 With WorksheetFunction Fibonacci = Round((.Power((1 + Sqr(5)) / 2, n) - .Power(2 / (1 + Sqr(5)), n) * .Power(-1, n)) / Sqr(5), 0) End With End Function HTH :) -- Dana DeLouis Win XP & Office 2003 wrote in message . .. Fibonacci function/command in excel?...tia sal Greetings all, Is thier a Fibonacci function or command built into excel? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Put this in module and you an use it in your sheet. Function Fibonacci(i) If i = 1 Or i = 2 Then tempsum = 1 Else t1 = 1 t2 = 1 For j = 3 To i ts = t1 + t2 t1 = t2 t2 = ts Next tempsum = ts End If Fibonacci = tempsum End Function -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=377129 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Hi. For a loop, the following is an interesting alternative.
Function Fibonacci(n) Dim v, t, x v = [{1,1;1,0}] t = v For x = 2 To CInt(n) v = WorksheetFunction.MMult(v, t) Next x Fibonacci = v(2, 1) End Function -- Dana DeLouis Win XP & Office 2003 "Kaak" wrote in message ... Put this in module and you an use it in your sheet. Function Fibonacci(i) If i = 1 Or i = 2 Then tempsum = 1 Else t1 = 1 t2 = 1 For j = 3 To i ts = t1 + t2 t1 = t2 t2 = ts Next tempsum = ts End If Fibonacci = tempsum End Function -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=377129 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Oops! Old notes! This does it with 1 less loop. :)
Function Fibonacci(n) Dim v, t, x v = [{1,1;1,0}] t = v For x = 2 To CInt(n - 1) v = WorksheetFunction.MMult(v, t) Next x Fibonacci = v(1, 1) End Function -- Dana DeLouis Win XP & Office 2003 <snip |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Nice application of MMULT but a bit of overkill, don't you think?
The following will do just fine: Function FibonacciNumber(ByVal N As Long) Dim I As Long, X0 As Variant, X1 As Variant X0 = 1: X1 = 1 For I = 3 To N Step 2 X0 = CDec(X0 + X1) X1 = CDec(X0 + X1) Next I FibonacciNumber = IIf(N Mod 2 = 1, X0, X1) End Function Sub testIt2() MsgBox FibonacciNumber(100) End Sub Without the CDec piece and with X0 and X1 declared as longs it works fine upto FibonacciNumber(46) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Oops! Old notes! This does it with 1 less loop. :) Function Fibonacci(n) Dim v, t, x v = [{1,1;1,0}] t = v For x = 2 To CInt(n - 1) v = WorksheetFunction.MMult(v, t) Next x Fibonacci = v(1, 1) End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Hi Tushar. Yes, it's definitely not too efficient. I just thought it was
interesting. :) I noticed that your example can't squeeze out the largest number possible...139 because X1 will overflow inside the loop. Perhaps as an idea, stop the loop just prior to X1 overflowing, and go from there. Perhaps: Function FibonacciNumber(ByVal n As Long) Dim I As Long, X0 As Variant, X1 As Variant X0 = CDec(1): X1 = X0 For I = 3 To (n - 1) Step 2 X0 = X0 + X1 X1 = X0 + X1 Next I FibonacciNumber = IIf(n Mod 2 = 1, X0 + X1, X1) End Function ?FibonacciNumber(139) 50095301248058391139327916261 Just for gee-wiz, there are additional neat techniques. For example, if N is an even number, one can cut the number of loops in half again. Function Fibonacci_Even(ByRef N As Long) Dim X As Variant Dim Y As Variant Dim j As Long Dim Half As Long '// For Even numbers only... If N Mod 2 = 1 Then Exit Function Select Case N Case Is < 2, Is 138: Fibonacci_Even = CVErr(9) 'Subscript out of range Case 2: Fibonacci_Even = 1 Case Else Half = N / 2 X = CDec(1): Y = X For j = 3 To Half - 1 Step 2 X = X + Y Y = X + Y Next j If Half Mod 2 = 0 Then Fibonacci_Even = Y * (2 * X + Y) Else Fibonacci_Even = (X + Y) * (X + 3 * Y) End If End Select End Function ?Fibonacci_Even(138) 30960598847965113057878492344 -- Dana DeLouis Win XP & Office 2003 "Tushar Mehta" wrote in message m... Nice application of MMULT but a bit of overkill, don't you think? The following will do just fine: Function FibonacciNumber(ByVal N As Long) Dim I As Long, X0 As Variant, X1 As Variant X0 = 1: X1 = 1 For I = 3 To N Step 2 X0 = CDec(X0 + X1) X1 = CDec(X0 + X1) Next I FibonacciNumber = IIf(N Mod 2 = 1, X0, X1) End Function Sub testIt2() MsgBox FibonacciNumber(100) End Sub Without the CDec piece and with X0 and X1 declared as longs it works fine upto FibonacciNumber(46) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Oops! Old notes! This does it with 1 less loop. :) Function Fibonacci(n) Dim v, t, x v = [{1,1;1,0}] t = v For x = 2 To CInt(n - 1) v = WorksheetFunction.MMult(v, t) Next x Fibonacci = v(1, 1) End Function |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fibonacci function/command in excel?...tia sal
Hi. I know this really doesn't apply here, but I thought you may find the
following topic interesting. I know that MMult was a bit of "overkill", but certain math programs take advantage of that with a more efficient version. The fastest algorithms look at the bit pattern of the number in binary form and choose 1 of 2 simple operations. They then use a more efficient form of MMult. For example, 128 (2^7) can be calculated in 7 loops. Sub Fib_128() Dim v, t, j '// Note: Log(128)/Log(2) = 7 v = Array(CDec(1), CDec(1), CDec(0)) For j = 1 To 7 t = v(1) * v(1) v = Array(v(0) * v(0) + t, v(1) * (v(0) + v(2)), t + v(2) * v(2)) Next j Debug.Print v(1) End Sub Returns: 251728825683549488150424261 The real speed comes from wanting to do Fibonacci (16384) (ie 2^14) where you only need to loop 7 more times. Excel can't do that directly, of course. The real code is written slightly more efficiently then that above. Anyway, just gee-wiz. :) The op probably only wanted Fibonacci (10). :) -- Dana DeLouis Win XP & Office 2003 "Tushar Mehta" wrote in message om... Oh, yes, it was interesting. But, then, so are many of your posts. I realized the limitation of the code I shared while writing it but figured...wtf... The alternative that I thought of was X0=1: X1=1 for i=3 to n Xtemp=CDec(X0+X1): X0=X1: X1=Xtemp next i FibonacciNumber=X1 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar. Yes, it's definitely not too efficient. I just thought it was interesting. :) I noticed that your example can't squeeze out the largest number possible...139 because X1 will overflow inside the loop. Perhaps as an idea, stop the loop just prior to X1 overflowing, and go from there. Perhaps: Function FibonacciNumber(ByVal n As Long) Dim I As Long, X0 As Variant, X1 As Variant X0 = CDec(1): X1 = X0 For I = 3 To (n - 1) Step 2 X0 = X0 + X1 X1 = X0 + X1 Next I FibonacciNumber = IIf(n Mod 2 = 1, X0 + X1, X1) End Function ?FibonacciNumber(139) 50095301248058391139327916261 Just for gee-wiz, there are additional neat techniques. For example, if N is an even number, one can cut the number of loops in half again. Function Fibonacci_Even(ByRef N As Long) Dim X As Variant Dim Y As Variant Dim j As Long Dim Half As Long '// For Even numbers only... If N Mod 2 = 1 Then Exit Function Select Case N Case Is < 2, Is 138: Fibonacci_Even = CVErr(9) 'Subscript out of range Case 2: Fibonacci_Even = 1 Case Else Half = N / 2 X = CDec(1): Y = X For j = 3 To Half - 1 Step 2 X = X + Y Y = X + Y Next j If Half Mod 2 = 0 Then Fibonacci_Even = Y * (2 * X + Y) Else Fibonacci_Even = (X + Y) * (X + 3 * Y) End If End Select End Function ?Fibonacci_Even(138) 30960598847965113057878492344 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to draw fibonacci numbers | Excel Discussion (Misc queries) | |||
fibonacci numbers | Charts and Charting in Excel | |||
Fibonacci function/command in excel?...tia sal | Excel Worksheet Functions | |||
Fibonacci function/command in excel?...tia sal | Excel Discussion (Misc queries) | |||
Fibonacci function/command in excel?...tia sal | Excel Worksheet Functions |