Generate the Fibonacci sequence in Excel with the SEQUENCE function

Generating the famous Fibonacci sequence is a super fun project to make in Excel, especially for mathematics and computer science teachers alike to challenge their students! 😉

My previous articles in this series were focusing mainly on using the SEQUENCE function to demonstrate how to repeat numbers in ascending order, repeat a sequence of numbers multiple times, generate a sequence of prime numbers. However, we need to think out of the box for the Fibonacci numbers, how to apply more than just the SEQUENCE function.

The main aim of this tutorial is to generate a Fibonacci sequence on demand i.e., 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89 and so on. We will be harnessing the power of Excel’s dynamic array formulas of the SEQUENCE function and the LAMBDA, REDUCE, MMULT functions and more. We will build some pretty cool custom formulas. 😎

If you’ve never heard of the Fibonacci sequence, than please read this. The basic idea is that each term of this sequence depends on the previous two terms i.e., Fₙ = Fₙ₋₁ + Fₙ₋₂ for n ∈ ℕ⁰ where F₀ = 0 and F₁ = 1. The Fibonacci numbers appear in nature as in the branching of trees, the arrangement of sunflower seeds spirals, pine cone spirals and more.

Leonardo Fibonacci theorized about the growth of an idealistic but unrealistic rabbit population. For example, we start on a farm with one newborn pair of rabbits i.e., one female and one male. Every pair is ready to mate once they reach the age of one month old and the pair gives birth to a single pair of rabbits the following month without fail. The rabbits are assumed to never die and to keep on reproducing forever. Leonardo’s question was something like, how many pairs of rabbits will there be on the (infinitely expanding) farm of supernatural bunnies after a number of months or years?

If you would rather prefer to skip all the explanations and jump straight to the formula, then please go directly to the summary! 🚀

Table of Contents

· Part I — The Iterative Algorithm
· Part II — Matrix Form
· Part III — The Recursive Function
· Part IV— The Closed-Form Formula
· Summary
· Bonus I — Understanding the INDIRECT, ADDRESS & OFFSET combo
· Bonus II — VBA Code to Copy Formula Down
· Bonus III— VBA Code for the Iterative Method
· Bonus IV— VBA Code for the Recursive Method
· Downloads

Part I — The Iterative Algorithm

The Fibonacci sequence is fairly trivial to create manually in Excel. Let’s say that we would like to work out the first ten Fibonacci terms according to the relation Fₙ = Fₙ₋₁ + Fₙ₋₂ for n ∈ ℕ⁰ with the initial values of F₀ = 0 and F₁ = 1:

If we consider Fibonacci’s question about the cosmic farm of super rabbits, then ideally, there should be 55 pairs of rabbits in 10 months time. 🐰

The formula text in column C pictured above should be self explanatory. Terms 0 and 1 are the initial values. We only start adding the previous two terms starting with term 2. Formula =B2+B3 in cell B4 is then dragged down to produce the next numbers, which works fine for a short sequence.

Speaking about iteration, the basic iterative algorithm for generating the Fibonacci sequence would use a loop like this:

Function BasicFibo(N As Long) As LongPtr()
    Dim sequence() As LongPtr
    ReDim sequence(1 To N)
    
    Dim a, b, t As LongPtr
    a = 0
    b = 1
    
    Dim i As Long
    For i = 1 To N
        t = a
        a = b
        b = t + b
        sequence(i) = a
    Next i
    
    BasicFibo = sequence
End Function

This is the basic idea as demonstrated in the spreadsheet. We start with the Fibonacci terms a=0 and b=1 as our initial values F₀ = 0 and F₁ = 1. Next, we use a temporary variable t to update the values for a and b. We add the value for variable a with every iteration.

This is the stripped back version of the code to demonstrate the basic iterative algorithm. Check out Bonus III to view in more detail the VBA code for implementing the User Defined Function (UDF) that computes the Fibonacci numbers iteratively and outputs them as a dynamic array.

Therefore, my goal is to call one function consisting of built-in Excel functions only, to generate the Fibonacci sequence in one go.

First we need to decide when to call the sum formula =B2+B3 i.e., for index N ≥ 2. Hence, we will try to “iterate” over the variable N as follows:

=IF(E2 < 2, E2, OFFSET(F2, -1, 0) + OFFSET(F2, -2, 0))

In this example, column E contains the numbers N that we are “iterating” over and column F contains the Fibonacci numbers. Note that I am using the OFFSET function to retrieve the previous two Fibonacci numbers in the sequence in column F only if N ≥ 2 in column E.

Drag down formula: =IF(E2 < 2, E2, OFFSET(F2, -1, 0) + OFFSET(F2, -2, 0))

To make the previous formula more dynamic, we can obtain the current address of the cell where we are in by running ADDRESS(ROW(), COLUMN()). This way, it doesn’t matter which cell you are running the formula from because the combined ADDRESS, ROW and COLUMN will return the current cell’s address. In turn, the address is used as an input parameter to the INDIRECT function to access the cell’s contents. To understand more about the combination of all these functions please see Bonus I.

Next, we can rewrite the entire formula in the dynamic form:

=IF(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1) < 2,
OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1),
OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), -1, 0)
+ OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), -2, 0))

Drag down the above formula for the number of “iterations” required and hey presto:

Unfortunately, the above formula doesn’t look very elegant and we shouldn’t have to write the nested INDIRECT, ADDRESS, ROW and COLUMN functions over and over again. We still depend on the sequence for N being adjacent to our nested formula.

Also, one has to drag the formula down for the number of cells required. Alternatively, select the target area including the first cell containing the formula, then press Ctrl+D to copy it down. This is an automation issue.

Let’s address the elegance of the formula by using the LAMBDA function to achieve a “cleaner” and automatable formula. We start with =LAMBDA(a, b, a + b)(A2, A3) to build the basic idea:

Instead of having two inputs, we can improve our formula by setting the current cell indirectly as input for parameter x and then offsetting x inside the sum:

=LAMBDA(x, OFFSET(x, -1, 0) + OFFSET(x, -2, 0))(
INDIRECT(ADDRESS(ROW(), COLUMN())))

Note that we do not require the number N to iterate anymore. We do still require the first two initial numbers and we need to drag the formula down manually.

We should take care of the #REF! error for when the previous cells are out of range i.e., row number < 1, the cells are blank, if they contain text or invalid numbers:

=LAMBDA(x, IF( OR( ISERROR(OFFSET(x, -1, 0)), ISBLANK(OFFSET(x, -1, 0)), 
NOT(ISNUMBER(OFFSET(x, -1, 0))) ), 0,
IF(AND( IFERROR(OFFSET(x, -1, 0) <> 0, FALSE), OR( ISERROR(OFFSET(x, -2, 0)), 
ISBLANK(OFFSET(x, -2, 0)), NOT(ISNUMBER(OFFSET(x, -2, 0))) ) ), 0,
IF(AND( IFERROR(OFFSET(x, -1, 0) = 0, FALSE), OR( ISERROR(OFFSET(x, -2, 0)), 
ISBLANK(OFFSET(x, -2, 0)), NOT(ISNUMBER(OFFSET(x, -2, 0))),
IFERROR(OFFSET(x, -2, 0) <> 0, FALSE) ) ), 1,
OFFSET(x, -1, 0) + OFFSET(x, -2, 0)
))))(INDIRECT(ADDRESS(ROW(), COLUMN())))

Admittedly, the validation statements make the new formula look quite complicated. So lets simplify it a bit more with the LET function:

=LAMBDA(x,
LET(
a, OFFSET(x, -2, 0),
b, OFFSET(x, -1, 0),
IF(OR(ISERROR(b), ISBLANK(b), NOT(ISNUMBER(b))), 0,
IF(AND(IFERROR(b <> 0, FALSE), OR( ISERROR(a),
ISBLANK(a), NOT(ISNUMBER(a)))), 0,
IF(AND( IFERROR(b = 0, FALSE), OR( ISERROR(a),
ISBLANK(a), NOT(ISNUMBER(a)),
IFERROR(a <> 0, FALSE))), 1,
a + b )))))(INDIRECT(ADDRESS(ROW(), COLUMN())))

Drag the new formula down, and it should look as follows:

For ease of use, it’s best to save the nested formula as “IterFiboOffset” in the Name Manager which can be found in the Formula ribbon. Make sure that you only copy and paste the =LAMBDA(...) portion of formula, not the inputs.

The “IterFiboOffset” can be used by simply calling the formula =IterFiboOffset( INDIRECT(ADDRESS(ROW(), COLUMN()))) in a cell, press Enter.

Then select the target range that we want the formula to be applied and press Ctrl+D to copy it down.

Disclaimer: I have spent many hours and lost some of my sanity 😵‍💫 in the process of trying to automate the “IterFiboOffset” function to produce a dynamic array of Fibonacci numbers. These attempts involved numerous combinations of the SEQUENCE, MAP, LAMBDA, MAKEARRAY, LET, REDUCE and VSTACK functions, but all in vain!

Unfortunately, nothing worked due to the recursive nature of the Fibonacci formula. And when we call ROW() and COLUMN() causes Circular Reference errors inside dynamic formulas. The use of the volatile INDIRECT and OFFSET functions is also problematic in combination with circular reference when trying to generate dynamic arrays.

The only way that I could create a dynamic array directly in Excel without VBA code is the old Ctrl+Shift+Enter (CSE) way. That is, select the whole target range, next start typing “=” in the first cell while the range is selected , then complete typing the formula =IterFiboOffset(INDIRECT( ADDRESS(ROW(), COLUMN()))), and then press Ctrl+Shift+Enter. Note the curly braces around the formula {=IterFiboOffset(INDIRECT(ADDRESS( ROW(), COLUMN())))} as they indicate that we have created a dynamic array formula.

The CSE method is still very manual, so I have written a short VBA subroutine to copy the formula down as shown in Bonus II.

Just as I gave up all hope, I have stumbled across this Microsoft Tech Community blog and found some truly mind-blowing stuff! 🤯

Lori M.’s formula here is absolutely GENIUS! 🤩 It uses the REDUCE function to accumulate terms. As it stacks the Fibonacci terms with the VSTACK formula, it makes sure to sum the last two terms from the vertical stack with the TAKE function. So simple, compact and elegant!

=REDUCE(1,SEQUENCE(n-1),LAMBDA(b,_,VSTACK(b,SUM(TAKE(b,-2)))))

You just have to admire the brilliance of this! Like really, take your time to digest this. And with David Leal’s improvement on Lori’s formula here, this is absolutely bulletproof!

=LAMBDA(n, IF(n=1, 1,
REDUCE(1,SEQUENCE(n-1), LAMBDA(b,_,VSTACK(b,SUM(TAKE(b,-2)))))))

This brilliant formula is as iterative as it’s going to get.

Note the syntax for the REDUCE function is as follows:

=REDUCE([initial_value], array, LAMBDA(accumulator, value, body))

Compared to Lori’s formula:

=REDUCE(1,SEQUENCE(n-1),LAMBDA(b,_,VSTACK(b,SUM(TAKE(b,-2)))))

With the input parameters as:

• initial_value = 1;
• array = SEQUENCE(n-1);
• accumulator = b i.e., b is where accumulate the totals for the previous — previous two terms. For example, if we are currently at term F(n) = F(n-1)+F(n-2) then b = F(n-1) = F(n-2)+F(n-3);
• value = _ to be added to the accumulator b is ignored, that is why Lori put as the underscore;
• body = VSTACK(b, SUM(TAKE(b, -2))) is the actual calculation that we want to reduce, and this is where the real magic happens! It basically stacks values {F(n-1), F(n-1)+F(n-2)} = {F(n-1), F(n)}.

I would recommend saving the lambda function for easy of use as perhaps “IterFiboReduce” in the Name Manager, found in the Formulas ribbon.

The following is my alternative visual explanation of Lori’s formula:

The formula keeps stacking the accumulated Fibonacci terms one after the other, like I have shown in column A. Then it keeps adding the last two terms by taking from the vertical stack, as I have done in column B:

=REDUCE(, TAKE($A$2:A3, -2), LAMBDA(a,b, a+b))

Hopefully my explanation makes sense.

Back to the Table of Contents.

Part II — Matrix Form

We can use linear algebra to produce the Fibonacci numbers. To start with, let’s rewrite the Fibonacci relationship as a system of two simultaneous linear equations in the following way:

• Fₙ₊₂ = Fₙ₊₁ + Fₙ
• Fₙ₊₁ = Fₙ₊₁

The coefficients of the previous terms can be extracted from the system as:

• Fₙ₊₂ = 1 ⋅ Fₙ₊₁ + 1 ⋅ Fₙ
• Fₙ₊₁ = 1 ⋅ Fₙ₊₁ + 0 ⋅ Fₙ

The simultaneous equations are then written in the matrix form using matrix multiplication where the 2-by-2 matrix represents the coefficients:

First, we create the coefficients matrix for the Fibonacci system in Excel:

=MAKEARRAY(2, 2, LAMBDA(r,c, IF(r*c < 3, 1, 0)))

Or simply enter the 2-D array in a cell: ={1, 1; 1, 1}.

We then create the index n sequence as a guide with the hardcoded initial values of F₀ = 0 and F₁ = 1:

Lastly, we perform the matrix multiplication using the MMULT function:

=MMULT($B$2#, B7#)

Drag this formula to the right to obtain the Fibonacci sequence which one would read the Fibonacci numbers in row 8.

Alternatively, we can just rewrite like so:

=LAMBDA(col_vector, MMULT({1,1;1,0}, col_vector))(B10#)

This process demonstrates matrix multiplication manually. Here is one way to automate the above matrix multiplication that I found on the Microsoft Tech Community blog.

Lori M.’s awesome formula here to generate the Nth term uses the REDUCE function to accumulate terms. However, it does have a small problem that it multiplies the accumulator with itself.

=INDEX(REDUCE({1,1;1,0},SEQUENCE(10),LAMBDA(a,_,MMULT(a,a))),2,1)

David Leal’s formula here once again improves on Lori’s formula, fixing the issue with Lori’s formula so that the accumulator does not multiply with itself, instead it multiplies with a = {1, 1; 1, 0}.

=LAMBDA(n, LET(a, {1,1;1,0}, 
IF(n=1,1, INDEX(REDUCE(a,SEQUENCE(n-1),LAMBDA(ac,_,MMULT(ac,a))),2,1))))

I have saved Lori & David’s formula as “FiboTermMatrix” in the Name Manager, and just plugged it straight into a MAKEARRAY function:

=LAMBDA(terms, MAKEARRAY(terms,1,LAMBDA(r,_, FiboTermMatrix(r))))

In turn, store the final matrix multiplication automation in the Name Manager as “FiboMatrixMult”.

If you prefer to do the matrix multiplication in a proper engineering program like MATLAB, the open-source alternative GNU Octave or Octave-Online, then here is one way to automate the above:

C = [1, 1; 1, 0];
F = [1; 0];

n = int32(input("Enter number of Fibonacci terms: "));

if n > 1
    for i = 1:n
        F=[F, C * F(:, end)];
    end
    disp(F(2, 2:end));
end

For example, we can save the code provided in a script in Octave-Online:

When you click the RUN button, you should get prompted in the console to input the number of terms:

The console should display the following 8 Fibonacci terms:

For those who have MATLAB licenses or the desktop GNU Octave, the process will be the same. Save the m-script and run it in the console. Piece of cake. 🍰

Back to the Table of Contents.

Part III — The Recursive Function

Recursion is an important topic in Computer Science with the Fibonacci sequence as one of the primary examples taught to students. Here is a nice resource if you would like to learn more about recursion.

Let’s dive straight in! We begin by rewriting the relationship Fₙ = Fₙ₋₁ + Fₙ₋₂ in the functional notation i.e., Fibo(N) = Fibo(N-1) + Fibo(N-2). This reveals the recursive nature of the Fibonacci sequence, meaning that we are recursively calling the “Fibo” function within itself.

We resort to simple VBA code here, to illustrate the recursion concept by calling the function inside itself. We adjust the input parameter N only. The function below outputs the Nth Fibonacci term:

Function FiboRecrInt(N As Long) As LongPtr
       
    If N < 2 Then
        FiboRecrInt = N
    Else
        FiboRecrInt = FiboRecrInt(N - 1) + FiboRecrInt(N - 2)
    End If
    
End Function

Recursion requires a particular stopping criteria to terminate the recursion. In our case, this criteria is N < 2. If you would like to see the full VBA implementation of the UDF, please check out Bonus IV.

Excel’s LAMBDA function is perfectly suited for recursion. We can rewrite the basic VBA code from above as the following Excel formula:

=LAMBDA(N, IF(N < 2, N, RecursiveFibo(N-1) + RecursiveFibo(N-2)))

This must be stored in the Name Manger in the Formulas ribbon, making sure the stored name “RecursiveFibo” is the same as in the LAMBDA formula. Otherwise, the LAMBDA function will not work!

What can we do with this? For starters, we can enter =RecursiveFibo(A2) in a cell and drag the formula down to obtain the Fibonacci sequence as follows:

Using the SEQUENCE function to generate N automatically as follows =LAMBDA(num, RecursiveFibo(num))(SEQUENCE(20)) did not work. However, I have managed to get the recursion function automated with MAKEARRAY:

=LAMBDA(num_terms, MAKEARRAY(num_terms, 1, LAMBDA(row, col, 
RecursiveFibo(row))))

As you may have noticed, we only require the row number as the input to “RecursiveFibo”. The column number is required by the MAKEARRAY dynamic array function but not used. Next, type this formula in one cell and enter the input number of terms:

Save this automated formula =LAMBDA(num_terms, MAKEARRAY(num_terms, 1, LAMBDA(row, col, RecursiveFibo(row)))) in Name Manager as “RecursiveFiboArray” to make more user friendly:

Simply enter the new formula with the number of terms and voila:

Warning: Do not go over 30 terms! If you input =RecursiveFiboArray(31) or higher, your Excel app will probably start hanging and eventually crash. This does depend on your machine’s setup. My Excel crashes on my machine which has 16 GB of RAM and an 11th Gen Intel Core CPU. This is all because recursion creates a stack of calls in memory and it very soon reaches its limit in Excel. More specifically, every time we call the underlying “RecursiveFibo” will create another stack in memory, which has to unwind to down 1 and then wind up back to N for every N. This gets complicated quickly. Best left to computer scientists.

Back to the Table of Contents.

Part IV— The Closed-Form Formula

The closed-form solution for the second-order (as we depend on two previous terms) linear recurrence relation xₙ = xₙ₋₁ + xₙ₋₂ with initial values x₀ = 0 and x₁ = 1, is:

This beautiful formula also known as “Binet’s formula”, might look a bit intimidating to non-mathematicians at first.

Why is it a ‘beautiful’ mathematical formula? It is because it contains the Golden Ratio ϕ. Let me explain. The first term has the Golden Ratio ϕ as the base i.e.,

where the second term’s base can be written with respect to the Divine proportion ϕ as follows:

So why is this Divine ratio showing up in the Fibonacci numbers?

Mathematically speaking, we can see that the ratio between two subsequent Fibonacci numbers will converge to the Golden Ratio for a large enough term number n i.e.,

In other words, we can solve for the ratio λ appearing in subsequent terms when n tends to infinity:

Substituting xₙ = xₙ₋₁ + xₙ₋₂, we get the following equality:

The fraction on the left simplifies to:

Substituting λ = xₙ₋₁ / xₙ₋₂ in the simplified equality will give us the equation λ² = λ + 1 (a.k.a. the characteristic equation for the 2nd order recursive relation) whose roots can be solved by completing the square:

The appearance of ϕ can be demonstrated easily in Excel as follows:

We reach a difference of zero to 15 significant figures in Excel at around Fibonacci term 38. Precision aside, we can clearly see the Golden Ratio is integral to the Fibonacci sequence.

To obtain Binet’s closed-form solution, we know from solving the characteristic equation λ² = λ + 1 that λ₁ = ϕ and λ₂ = 1 - ϕ are solutions to the Fibonacci relation.

Therefore, the general solution to the second-order linear recurrence relation xₙ = xₙ₋₁ + xₙ₋₂ must be of the form xₙ = A ϕⁿ + B (1 — ϕ)ⁿ. More precisely, we write the general solution as:

where the constants A and B depends on the initial conditions x₀ and x₁.

Given the initial values x₀ = 0 and x₁ = 1, we obtain a system of two linear equations:

• A + B = 0
• A ϕ + B (1 - ϕ) = 1

Solving the system, we get A = 1 / √5 and B = -1 / √5. Substituting A and B back into the general solution we obtain Binet’s closed form solution.

This is by far, the simplest way to automate generating Fibonacci numbers. Binet’s formula lends itself perfectly to the LAMBDA function:

=LAMBDA(n, LET(phi, (1+SQRT(5))/2, (phi^n - (1-phi)^n)/SQRT(5)))

Let’s start automating this formula to achieve our goal of generating the Fibonacci sequence in one go. First, we need to pass a sequence of integers for the term index n as follows:

=LAMBDA(n, LET(phi, (1+SQRT(5))/2, (phi^n - (1-phi)^n)/SQRT(5)))(
SEQUENCE(1001,,0))

The second step is to pass the number of terms as a parameter. So we nest the closed-form lambda formula inside another formula.

=LAMBDA(num_terms, 
LAMBDA(n, LET(phi, (1+SQRT(5))/2, (phi^n - (1-phi)^n)/SQRT(5)))(
SEQUENCE(num_terms + 1,,0)))(1000)

The last step is to save the whole nested formula as “GoldenFiboArray” without the parameter (1000) at the end of the formula in the Name Manager in the Formulas ribbon:

The saved function “GoldenFiboArray” can be easily accessed in any cell:

And voila, the first 1000 Fibonacci numbers generated in an instant:

Back to the Table of Contents.

Summary

Here is a quick reference list of the most important formulas discussed in this article.

Summary — Iterative method

My offset formula is nothing to write home about. It is problematic and it can only be “automated” via the Ctrl-Shift-Enter method which still require you to select the target area.

=LAMBDA(x, OFFSET(x, -1, 0) + OFFSET(x, -2, 0))(INDIRECT(ADDRESS(ROW(), COLUMN())))

However, Lori & David’s formula to generate the Fibonacci sequence iteratively is simply mind-blowing! You will need to input the number of terms required after the LAMBDA.

=LAMBDA(n, IF(n=1, 1, REDUCE(1,SEQUENCE(n-1), LAMBDA(b,_,VSTACK(b,SUM(TAKE(b,-2)))))))

You can refer to my explanation of their formula here.

Summary — Matrix Multiplication

The basic idea is pretty straight forward. We need to start with the initial conditions vector for example in cell A1:

=VSTACK(1, 0)

Then type the following in the adjacent formula, and drag it to the right as far as you would like:

=LAMBDA(col_vector, MMULT({1,1;1,0}, col_vector))(A1#)

For the automated version, we refer to Lori & David’s elegant formula to generate the N-th Fibonacci term:

=LAMBDA(n, LET(a, {1,1;1,0}, IF(n=1,1, INDEX(REDUCE(a,SEQUENCE(n-1),LAMBDA(ac,_,MMULT(ac,a))),2,1))))

We save their formula as say “FiboTermMatrix” in the Name Manager to automate the Fibonacci sequence up to specified number of terms:

=LAMBDA(terms, MAKEARRAY(terms,1,LAMBDA(r,_, FiboTermMatrix(r))))

Alternatively, you can use something like https://octave-online.net/.

Summary — Recursion

The classic computer science problem. Firstly, we must be store the N-th term recursive function in the Name Manger such as “RecursiveFibo”:

=LAMBDA(N, IF(N < 2, N, RecursiveFibo(N-1) + RecursiveFibo(N-2)))

Then we can call the N-th term function to generate the Fibonacci sequence up to specified number of terms*.

=LAMBDA(num_terms, MAKEARRAY(num_terms, 1, LAMBDA(row, col, RecursiveFibo(row))))

*Care must be taken with regards to the number of terms. Recursion is computationally costly! In my case, my Excel app freezes for more than 30 terms.

Summary — Golden Ratio

Binet’s closed-form solution involves the Golden Ratio and it is my favourite! This is the solution for the N-th term:

=LAMBDA(n, LET(phi, (1+SQRT(5))/2, (phi^n - (1-phi)^n)/SQRT(5)))

My way of automating the Fibonacci sequence:

=LAMBDA(num_terms, LAMBDA(n, LET(phi, (1+SQRT(5))/2, (phi^n - (1-phi)^n)/SQRT(5)))(SEQUENCE(num_terms + 1,,0)))

You are more than welcome to check out the VBA code below, if you prefer programming.

Back to the Table of Contents.

Bonus I — Understanding the INDIRECT, ADDRESS & OFFSET combo

Try the combination =ADDRESS(ROW(), COLUMN()) by itself in a cell and it should return the address for the cell that you are calling it from:

To obtain the value in the cell at that address, we have to nest the address formula in the INDIRECT formula.

Note: It does not make sense to call the value of the current cell inside itself and this should result in circular reference.

However, using the INDIRECT reference to the current cell in connection with the OFFSET function to obtain the value from a cell offset relative to the current cell does make sense! For example, obtaining the value from the previous cell above is done as follows:

The nice thing about this is that you can run the nested formula =OFFSET( INDIRECT(ADDRESS(ROW(), COLUMN())), -1, 0) in almost any cell. As long as you don’t run it in the first row which returns a reference error #REF!.

Back to the Table of Contents.

Bonus II — VBA Code to Copy Formula Down

Here is an idea for the VBA code that copies a formula down vertically, similar to Ctrl+D in a selected range:

Option Explicit
Option Base 1

Public Sub RepeatFunctionDown()
    Dim SelectedRange As Range
    Set SelectedRange = Selection.Cells(1)
    
    Dim FormulaText As String
    FormulaText = SelectedRange.FormulaR1C1
    
    If Trim(FormulaText) <> vbNullString Then
        Dim UserInput As Integer
        UserInput = MsgBox("Would you like to copy down the following formula?" & vbCrLf & FormulaText, vbYesNo)
        
        If UserInput = vbYes Then
            Dim NumberOfRepeats As Long
            NumberOfRepeats = CLng(Application.InputBox(prompt:="Number of Repeats: ", Title:=FormulaText, Type:=1))
            
            If NumberOfRepeats > 0 Then
                Dim rep As Long
                For rep = 1 To NumberOfRepeats
                    SelectedRange.Offset(rep, 0).FormulaR1C1 = FormulaText
                Next rep
            Else
                SelectedRange.Offset(1, 0).Value = CVErr(xlErrValue)
            End If
        End If
    Else
        MsgBox "Selected cell is empty!", vbInformation
    End If
End Sub

This subroutine depends on the user selecting a cell which contains a formula. For example, start by writing the formula that you would like to copy down in one cell only:

Then in your Developer ribbon, click on the Macros tab to open the Macros dialogue box. Alternatively click on Alt+F8 to open the Macros.

Go to the Developer ribbon and open Macros.

You should see the Macros dialogue box next:

The formula will be read from the selected cell and should be prompted in the following message box:

Click “Yes” and you should be prompted with an input box to enter the number of repeats:

The selected formula should be copied down the number of times that you have specified.

Back to the Table of Contents.

Bonus III— VBA Code for the Iterative Method

The more elaborate version for the “iterative” method of generating the Fibonacci numbers contains validations and error handling:

Option Explicit
Option Base 1

Private Function IterFiboInt(N As Long) As LongPtr()
    On Error GoTo error_handler
    
    Dim sequence() As LongPtr
    
    If N < 1 Then
        ReDim sequence(1 To 1)
        sequence(1) = 0
        GoTo end_fibo
    End If
    
    Dim a, b, t As LongPtr
    a = 0
    b = 1
    
    ReDim sequence(1 To N)
    Dim i As Long
    For i = 1 To N
        t = a
        a = b
        On Error Resume Next
        b = t + b
        If Err.Number = 6 Then
            ' Overflow
            On Error GoTo 0
            GoTo end_fibo
        End If
        If Err.Number <> 0 Then GoTo error_handler
        sequence(i) = a
    Next i
    
end_fibo:
    IterFiboInt = sequence
    Exit Function
error_handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, _
        Title:="Generate Fibonacci Sequence Iteratively"
    On Error GoTo 0
End Function

We call the function “IterFiboInt” inside the following function to turn it into a User Defined Function (UDF):

Public Function UDF_FiboSequenceIterativeInt(N As Long) As Variant
    Application.Volatile
    
    If N > 0 Then
        UDF_FiboSequenceIterativeInt = Application.Transpose(IterFiboInt(N))
    Else
        UDF_FiboSequenceIterativeInt = CVErr(xlErrValue)
    End If
End Function

The UDF can be called directly in a cell to return a dynamic array of Fibonacci numbers:

Note: Declaring theFibonacci terms as the LongPtr data type has some limitations i.e., we run into overflow issues. If you want your UDF to generate more than 92 Fibonacci terms, then you need to declare the Fibonacci terms as the data type Double.

Here is a comparison for generating the Fibonacci sequence with the Ctrl+Shift+Enter (CSE) method in column C, the UDF generating numbers as LongPtr integers in column D, and the UDF generating Double type numbers in column E:

The long pointer integer UDF overflows after 92 terms, where the double type carries one without a problem. The VBA code for the double type UDF can be found in the download.

Back to the Table of Contents.

Bonus IV— VBA Code for the Recursive Method

The recursive code to generate the Nth Fibonacci term is as follows

Option Explicit
Option Base 1

Private Function FiboRecrInt(N As Long) As LongPtr
       
    If N < 2 Then
        FiboRecrInt = N
    Else
        FiboRecrInt = FiboRecrInt(N - 1) + FiboRecrInt(N - 2)
    End If
        
End Function

In turn, the recursive function “FiboRecrInt” is called to populate the sequence of Fibonacci numbers:

Private Function FiboRecrSequenceInt(N As Long) As LongPtr()
    
    On Error GoTo error_handler
    
    Dim sequence() As LongPtr
    
    If N < 1 Then
        ReDim sequence(1 To 1)
        sequence(1) = 0
        GoTo end_fibo
    End If
       
    ReDim sequence(1 To N)
    Dim i As Long
    Dim Fib As LongPtr
    For i = 1 To N
        On Error Resume Next
        Fib = FiboRecrInt(i)
        If Err.Number = 6 Then
            On Error GoTo 0
            GoTo end_fibo
        End If
        If Err.Number <> 0 Then GoTo error_handler
        sequence(i) = Fib
    Next i
    
end_fibo:
    FiboRecrSequenceInt = sequence
    Exit Function
    
error_handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, _
        Title:="Generate Fibonacci Sequence Recursively"
    On Error GoTo 0
    
End Function

Lastly, we call the sequence generator “FiboRecrSequenceInt” in the following function to create the User Defined Function:

Public Function UDF_FiboSequenceRecursive(N As Long) As Variant
' Excel freezes and crashes for N > 30, depending on your system config.

    Application.Volatile
    
    If N > 0 Then
        UDF_FiboSequenceRecursive = Application.Transpose(FiboRecrSequenceInt(N))
    Else
        UDF_FiboSequenceRecursive = CVErr(xlErrValue)
    End If

End Function

Now we can call “UDF_FiboSequenceRecursive” in any cell:

Note: Please keep in mind the limitation of your system’s configuration for this recursive algorithm!

Back to the Table of Contents.

Downloads

You are more than welcome to download the example workbook here. Thank you and I hope you enjoyed this article. 🙏

Before you open the file downloaded from GitHub for the first time, make sure to right-click on the file and select Properties. In General > Security, select the “Unblock” check-box and then click the “Apply” button.

Back to the top.