Closed Thread
Results 1 to 10 of 10

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

  1. #1
    Guest

    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. #2
    Forum Contributor
    Join Date
    03-24-2004
    Location
    Edam Netherlands
    Posts
    181
    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

  3. #3
    JE McGimpsey
    Guest

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

    No.

    In article <NVjpe.62544$lQ3.38399@bignews5.bellsouth.net>,
    <sal@spp.net> wrote:

    > Is thier a Fibonacci function or command built into excel?


  4. #4
    Dana DeLouis
    Guest

    Re: 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


    <sal@spp.net> wrote in message
    news:NVjpe.62544$lQ3.38399@bignews5.bellsouth.net...
    > Fibonacci function/command in excel?...tia sal
    >
    > Greetings all,
    >
    > Is thier a Fibonacci function or command built into excel?
    >
    > TIA
    >
    >




  5. #5
    Dana DeLouis
    Guest

    Re: 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" <Kaak.1q9pyh_1118167523.3289@excelforum-nospam.com> wrote in message
    news:Kaak.1q9pyh_1118167523.3289@excelforum-nospam.com...
    >
    > 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. #6
    Dana DeLouis
    Guest

    Re: 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. #7
    Tushar Mehta
    Guest

    Re: 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 <u0mJUc#aFHA.1660@tk2msftngp13.phx.gbl>,
    delouis@bellsouth.net 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. #8
    Dana DeLouis
    Guest

    Re: 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
    news:MPG.1d10a0db46d5c3f98b028@msnews.microsoft.com...
    > 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 <u0mJUc#aFHA.1660@tk2msftngp13.phx.gbl>,
    > delouis@bellsouth.net 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
    >>
    >>




  9. #9
    Tushar Mehta
    Guest

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

    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 <uftE84DbFHA.2876@TK2MSFTNGP10.phx.gbl>,
    delouis@bellsouth.net 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
    >
    >


  10. #10
    Dana DeLouis
    Guest

    Re: 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
    news:MPG.1d10e561764520e298b02d@msnews.microsoft.com...
    > 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 <uftE84DbFHA.2876@TK2MSFTNGP10.phx.gbl>,
    > delouis@bellsouth.net 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
    >>
    >>




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1