L_FIBONACCI

=L_FIBONACCI(n, [sequence_tf])

Download the Template

In the template file, navigate to the Sequences worksheet to see the L_FIBONACCI function in action.

Description

The Fibonacci sequence is fun, cool, and fascinating. Beginning with 0, then 1, the next value in the sequence is the sum of the previous two values, so F_n = F_n-2 + F_n-1.

The function L_FIBONACCI returns the first N values of the Fibonacci Sequence, using recursion via the REDUCE function.

=L_FIBONACCI(15)

Result: {0;1;1;2;3;5;8;13;21;34;55;89;144;233;377}

The Golden Ratio

One of the most fascinating aspects of the Fibonacci sequence is the Golden Ratio, φ=(1+SQRT(5))/2 ≈ 1.61803. As the sequence approaches infinity, the ratio F_n/F_n-1 converges to the Golden Ratio. This special ratio is found in many different places in nature and tends to represent beauty, growth or perfection. Classic examples are the spiral of the nautilus shell and various human proportions. You might say that the Golden Ratio is actually closer to the answer to "Life, the Universe, and Everything" than the number 42.

Binet's Formula

To return just a single value from the set of Fibonacci numbers, the function uses Binet's Formula described in the example below. If you set the sequence parameter to FALSE, L_FIBONACCI(n,FALSE) will return just the n-th Fibonacci number from the series, F_n (starting at F₀=0).

Lambda Formula

This code for using L_FIBONACCI in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Returns the first N values in the Fibonacci Sequence
*/
L_FIBONACCI = LAMBDA(n,[sequence_tf],
IF(OR(ROWS(n)>1,COLUMNS(n)>1,n<1,n<>INT(n)),"Error: n should be an integer > 0",
LET(doc,"https://www.vertex42.com/lambda/fibonacci.html",
    sequence_tf,IF(ISBLANK(sequence_tf),TRUE,sequence_tf),
    IF(sequence_tf=TRUE,
        IF(n=1,0,IF(n=2,{0;1},
            REDUCE({0;1},SEQUENCE(n-2,1,3,1),LAMBDA(acc,i,
                VSTACK(acc,INDEX(acc,i-2)+INDEX(acc,i-1))
            ))
        )),
        LET(
            phi, (1+SQRT(5))/2,
            psi, (1-SQRT(5))/2,
            (phi^n-psi^n)/(phi-psi)
        )
    )
)));

Name: L_FIBONACCI
Description: Return the first N values in the Fibonacci Sequence
Arguments: n, sequence_tf
Function:
[same as the Excel version]

L_FIBONACCI Examples

Test: Copy and Paste this LET function into a cell
=LET(
    phi, (1 + SQRT(5)) / 2,
    psi, (1 - SQRT(5)) / 2,
    n, 42,
    (phi^n - psi^n) / (phi - psi)
)
=L_BINET(42)

Result: 267914296

Test: Copy and Paste this LET function into a cell
=LET(
    fib, DROP(L_FIBONACCI(15),2),
    DROP(fib,1)/DROP(fib,-1)
)

Result:
2
1.5
1.6666667
1.6
1.625
1.6153846
1.6190476
1.6176471
1.6181818
1.6179775
1.6180556
1.6180257

=L_BINET(25)/L_BINET(24)
=75025/46368

Result: 1.61803398874989