To dynamically generate a Fibonacci sequence, you can use the REDUCE function, one of the Lambda helper functions (LHFs) in Google Sheets.

The Fibonacci sequence is a series of numbers where each number is the sum of the two preceding ones, often starting with 0 and 1:

{0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, ...}

Dynamic Formula to Create a Fibonacci Sequence in Google Sheets:

=REDUCE(
   {0;1}, 
   SEQUENCE(MIN(N(B1), 1476)-1), 
   LAMBDA(
      acc, val, 
      LET(
         x, CHOOSEROWS(acc, -1), 
         y, CHOOSEROWS(acc, -2), 
         VSTACK(acc, x+y)
      )
   )
)

Enter a value between 0 and 1476, inclusive, in cell B1. This will represent the nth position of the Fibonacci sequence. Then, paste the formula into the first cell of an empty column, such as cell A1.

For example, if you enter 10 in cell B1, the formula will return the Fibonacci sequence from the 0th to the 10th Fibonacci number: {0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55}.

Note that Google Sheets can accurately calculate Fibonacci numbers up to the 1476th term (from 0 to 1476). Beyond this point, the numbers may exceed the display limits of Google Sheets, causing precision issues.

Step 1: Setting Up the Input

The Fibonacci sequence starts from 0. Enter 10 in cell B1 to generate the first 11 Fibonacci numbers, from the 0th to the 10th Fibonacci number. The result will be in rows A1 to A11.

This is because the formula includes the array constant {0; 1} and the SEQUENCE function generates 9 numbers (B1-1), plus the initial values, resulting in a total of 11 numbers.

Specifying {0; 1} is a common approach to initialize and generate the Fibonacci sequence in Google Sheets.

Step 2: Generating the Fibonacci Sequence

To generate the Fibonacci sequence, enter the following formula in cell A1:

=REDUCE(
   {0;1}, 
   SEQUENCE(MIN(N(B1), 1476)-1), 
   LAMBDA(
      acc, val, 
      LET(
         a, CHOOSEROWS(acc, -1), 
         b, CHOOSEROWS(acc, -2), 
         VSTACK(acc, a+b)
      )
   )
)

If the formula returns a #REF! error, the column is not empty. The formula requires an empty column because it’s a dynamic array formula.

The sequence will adjust automatically based on the number specified in cell B1. You can specify any number, but the formula will use a minimum value of 0 and a maximum value of 1476.

Step 3: Finding the Nth Fibonacci Number

To get the nth Fibonacci number, use either of the following formulas in cell C1:

=CHOOSEROWS(A1:A, n+1)
=INDEX(A1:A, n+1)

For example, to calculate the 50th Fibonacci number, enter 50 in cell B1 and use the formulas with n replaced by 50.

=CHOOSEROWS(A1:A, 50+1)
=INDEX(A1:A, 50+1)

Optional: Formula Explanation

The following section provides a detailed explanation of the formula used to generate the Fibonacci sequence. If you are comfortable with the formula and its application, you can skip this part and proceed directly to using the formula.

We use the REDUCE function, one of the Lambda helper functions, to generate the Fibonacci sequence in Google Sheets. This function reduces an array by applying a specified operation iteratively to accumulate a result.

Syntax:

REDUCE(initial_value, array_or_range, lambda)

• initial_value: {0; 1} — These are the initial values in the accumulator.
• array_or_range: SEQUENCE(MIN(N(B1), 1476)-1) — This specifies the array or range to be reduced. It generates a sequence of numbers up to the number specified in cell B1, minus 1. If you enter 5 in cell B1, it will return {1, 2, 3, 4}.
  You can specify any number in cell B1. If you enter a number less than 0 or leave the cell empty, the formula defaults to 0. If you enter a number greater than 1476, the formula defaults to 1476.
• lambda:

LAMBDA(
      acc, val, 
      LET(
         x, CHOOSEROWS(acc, -1), 
         y, CHOOSEROWS(acc, -2), 
         VSTACK(acc, x+y)
      )
   )

In the Lambda function, acc and val are placeholders for the accumulator and array_or_range values, respectively.

• acc (accumulator) initially contains {0, 1}, and val (current element in the array) contains the sequence number, initially 1. The formula iterates over each value in the array_or_range, but val is not used in the calculations; it simply controls the iteration.

Lambda Explanation:

The REDUCE function iterates through the sequence and applies the Lambda function to each element, reducing the array step by step.

Within the Lambda function, the LET function assigns two preceding values from the accumulator to variables x and y.

In the formula, x and y are added together and then vertically stacked with the accumulator to dynamically build the Fibonacci sequence.

Limitations of Google Sheets for Fibonacci Calculations

Google Sheets can accurately calculate Fibonacci numbers up to the 1476th term, which is approximately 1.30699E+308. Beyond this point, numbers may exceed the display limits of Google Sheets, leading to precision issues and typically returning a #NUM! error.

The dynamic formula provided will handle errors caused by these limitations. If you specify a number greater than 1476 in cell B1, the formula will default to 1476. Similarly, if you specify a number less than 0, the formula will default to 0. This approach helps improve the performance of the formula.

Please keep these limitations in mind when using the dynamic array formula to calculate the Fibonacci sequence in Google Sheets.

Calculating Fibonacci Numbers with a Simple Non-Array Formula

If you prefer a non-array formula to generate the Fibonacci sequence, follow these steps in Google Sheets:

1. Enter 0 in cell A1.
2. Enter 1 in cell A2.
3. In cell A3, enter the following formula and drag the fill handle down as far as you want:

=A1+A2

You can drag it down until row 1477. Beyond that, the formula will return a #NUM! error, similar to the dynamic array formula.

Resources

1. Create Custom Time Slot Sequences in Google Sheets
2. Generating a Sequence of Months in Google Sheets
3. Fill a Column with a Sequence of Decimals in Google Sheets
4. How to Populate Sequential Dates Excluding Weekends in Google Sheets
5. Normal and Array-Based Running Total Formulas in Google Sheets
6. Running Count in Google Sheets – Formula Examples
7. How to Calculate Running Balance in Google Sheets (SUMIF and SCAN Solutions)