Fibonacci sequence in spreadsheets: recursive column and direct Nth term (Binet)

Recursive addition vs closed-form Nth Fibonacci lookup

The Fibonacci sequence starts F(0) = 0, F(1) = 1, and adds the two preceding terms F(n) = F(n-1) + F(n-2) to produce 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55—widely used for Agile estimation scales and mathematical modeling.

When you need F(n) directly in one cell without helper rows, Binet's formula uses the Golden Ratio phi = (1 + SQRT(5)) / 2 (approx 1.618034) and psi = (1 - SQRT(5)) / 2: F(n) = ROUND((phi^n - psi^n) / SQRT(5), 0).

Closed-form Binet demonstration

Floating-point exponentiation eventually loses integer accuracy. Use modest nonnegative integer indices for this demonstration; exact large terms require integer recurrence with sufficient precision.

=LET(phi, (1 + SQRT(5)) / 2, psi, (1 - SQRT(5)) / 2, ROUND((phi^A2 - psi^A2) / SQRT(5), 0))

Golden ratio rounded to six decimals

Equals (1 + SQRT(5)) / 2 = 1.6180339887...

=ROUND((1 + SQRT(5)) / 2, 6)

Inputs and interpretation

Validate n as a nonnegative integer. Rounding a floating-point result does not make it exact at arbitrary n. The adjacent ratio is undefined where its denominator is F(0).

Further reading

• Microsoft Support — SQRT function