Fibonacci function/command in excel?...tia sal

Greetings all,

Is thier a Fibonacci function or command built into excel?

TIA

No.

In article ,
wrote:

Is thier a Fibonacci function or command built into excel?

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

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

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

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

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

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