A Fibonacci number is a sum of previous two terms starting with 0 & 1. Hence, 0, 1, 1, 2, 3, 5, 8, 13, 21, 34….are Fibonacci numbers. Find the Nth Fibonacci number.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 339
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find the Nth Fibonacci number with Power Query
Power Query solution 1 for Find the Nth Fibonacci number, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
p = (_, p) => Number.Power(_, p),
s = p(5, 0.5)
in
Int64.From((p((1 + s) / 2, [N]) - p((1 - s) / 2, [N])) / s)
)
in
SPower Query solution 2 for Find the Nth Fibonacci number, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each List.Accumulate({1 .. [N] - 2}, {1, 1}, (s, c) => s & {List.Sum(List.LastN(s, 2))}){
[N] - 1
}
)
in
SPower Query solution 3 for Find the Nth Fibonacci number, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer Expected",
each Number.Round(Number.Power(((1 + Number.Sqrt(5)) / 2), [N]) / Number.Sqrt(5), 0)
)
in
AnswerPower Query solution 4 for Find the Nth Fibonacci number, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Number.Round(
Number.Power(((1 + (Number.Power(5, 0.5))) / 2), [N]) / Number.Power(5, 0.5),
0
)
)
in
resPower Query solution 5 for Find the Nth Fibonacci number, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"N", Int64.Type}}),
Answer = Table.AddColumn(
#"Changed Type",
"Answer Expected",
each Number.Round(Number.Power(0.5 * (1 + Number.Sqrt(5)), [N]) / Number.Sqrt(5)),
Int64.Type
)
in
AnswerPower Query solution 6 for Find the Nth Fibonacci number, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
n = x,
f = List.Generate(
() => [x = 0, y = 1, c = 1],
each [c] <= 74,
each [y = [x] + [y], x = [y], c = [c] + 1],
each [x]
),
p = List.Positions(f),
z = List.Zip({f, p}),
s = List.Select(z, each _{1} = n),
r = List.Transform(s, each List.RemoveLastN(_)){0}
in
r,
p = Table.AddColumn(Origen, "Answer Expected", each Fx([N])),
Sol = Table.TransformColumns(
p,
{"Answer Expected", each Text.Combine(List.Transform(_, Text.From))}
)
in
SolSolving the challenge of Find the Nth Fibonacci number with Excel
Excel solution 1 for Find the Nth Fibonacci number, proposed by Bo Rydobon 🇹🇭:
=LET(n,
A2:A10,
f,
5^0.5,
((1+f)^n-(1-f)^n)/(2^n*f))Excel solution 2 for Find the Nth Fibonacci number, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
MAX(
REDUCE(
1,
SEQUENCE(
x-1
),
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
TAKE(
a,
-2
)
)
)
)
)
)
)
)
✅ Recursive:
=LET(
r,
LAMBDA(
r,
a,
b,
i,
n,
IF(
i=n,
a,
r(
r,
b,
a+b,
1+i,
n
)
)
),
MAP(
A2:A10,
LAMBDA(
x,
r(
r,
,
1,
,
x
)
)
)
)Excel solution 3 for Find the Nth Fibonacci number, proposed by محمد حلمي:
=INDEX(REDUCE({0;1},SEQUENCE(72),LAMBDA(a,d,
VSTACK(a,SUM(TAKE(a,-2))))),A2:A10+1)Excel solution 4 for Find the Nth Fibonacci number, proposed by محمد حلمي:
=ROUND((1+5^0.5)^A2:A10/(2^A2:A10*5^0.5),
)Excel solution 5 for Find the Nth Fibonacci number, proposed by Kris Jaganah:
=ROUND((((1+(5^0.5))/2)^A2:A10/(5^0.5)),
0)Excel solution 6 for Find the Nth Fibonacci number, proposed by Julian Poeltl:
=LET(N,
A2:A10,
1/SQRT(
5
)*(((1+SQRT(
5
))/2)^N-((1-SQRT(
5
))/2)^N))Excel solution 7 for Find the Nth Fibonacci number, proposed by Timothée BLIOT:
=INDEX(
REDUCE(
{0;1},
SEQUENCE(
73
),
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
TAKE(
a,
-2
)
)
)
)
),
A2:A10+1
)Excel solution 8 for Find the Nth Fibonacci number, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(a,
ROUNDUP((1 + SQRT(
5
)) ^ a / (2 ^ a * SQRT(
5
)),
0)
)
)Excel solution 9 for Find the Nth Fibonacci number, proposed by LEONARD OCHEA 🇷🇴:
=LET(s,
A2:A10,
k,
5^0.5,
(((1+k)/2)^s-((1-k)/2)^s)/k)Excel solution 10 for Find the Nth Fibonacci number, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
x+1,
f,
LAMBDA(
f,
x,
y,
a,
IF(
COUNT(
a
)>=n,
a,
f(
f,
y,
x+y,
VSTACK(
a,
x+y
)
)
)
),
TAKE(
f(
f,
0,
1,
{0;1}
),
-1
)
)
)
)Excel solution 11 for Find the Nth Fibonacci number, proposed by Charles Roldan:
=LET(_Y,LAMBDA(f,LAMBDA(x,x(x))(LAMBDA(x,f(LAMBDA(y,x(x)(y)))))),
_Next, LAMBDA(x, VSTACK(x, SUM(TAKE(x, -2)))),
_Fibs, _Y(LAMBDA(x, LAMBDA(n, IF(n>2, _Next(x(n-1)), {1;1})))),
INDEX(_Fibs(MAX(A2:A10)), A2:A10))
Here's one that utilizes the limited in-house recursion capabilities of Excel by constantly alternating between an array and a text string:
=INDEX(--TEXTBEFORE(SCAN(
"0, 1",SEQUENCE(MAX(A2:A10)),LAMBDA(x,y,
ARRAYTOTEXT(MMULT(--TEXTSPLIT(x,", "),{1,1;1,0})))),","),A2:A10)
And here's a short one I already am seeing pop up here:
=ROUND(((1+SQRT(5))/2)^A2:A10/SQRT(5),0)Excel solution 12 for Find the Nth Fibonacci number, proposed by Julien Lacaze:
=36 and over.
My solution anyway :
=LET(
fib,
LAMBDA(
n,
f,
IF(
n=0,
0,
IF(
n=1,
1,
f(
n-1,
f
)+f(
n-2,
f
)
)
)
),
fib(
A2,
fib
)
)Excel solution 13 for Find the Nth Fibonacci number, proposed by Pieter de Bruijn:
=ROUND((1+5^0.5)^A2:A10/(2^A2:A10*5^0.5),
)
or
=INDEX(
REDUCE(
{0;1},
SEQUENCE(
MAX(
A2:A10
)
),
LAMBDA(
x,
y,
VSTACK(
x,
SUM(
TAKE(
x,
-2
)
)
)
)
),
A2:A10+1
)Excel solution 14 for Find the Nth Fibonacci number, proposed by Giorgi Goderdzishvili:
=LET(
lst,
SCAN(
"0!1",
SEQUENCE(
75
),
LAMBDA(
a,
v,
CONCAT(
TAKE(
TEXTSPLIT(
a,
"!"
),
,
-1
),
"!",
SUM(
--TEXTSPLIT(
a,
"!"
)
)
)
)
),
tx,
1*TEXTBEFORE(
lst,
"!"
),
INDEX(
tx,
A2:A10,
1
)
)Excel solution 15 for Find the Nth Fibonacci number, proposed by Edwin Tisnado:
=LET(t,
(1+5^0.5)/2,
(t^A2:A10-(-t)^-A2:A10)/5^0.5)Excel solution 16 for Find the Nth Fibonacci number, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(
A2:A10,
LAMBDA(
a,
INDEX(
REDUCE(
{0;1},
SEQUENCE(
a
),
LAMBDA(
x,
y,
VSTACK(
x,
SUM(
TAKE(
x,
-2
)
)
)
)
),
a+1
)
)
)Excel solution 17 for Find the Nth Fibonacci number, proposed by Juliano Santos Lima:
=ROUND(((1 + SQRT(5))^N - (1 - SQRT(5))^N) / (2^N * SQRT(5)); 0)Solving the challenge of Find the Nth Fibonacci number with R
R solution 1 for Find the Nth Fibonacci number, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(memoise)
input = read_excel("Nth Fibonacci Number.xlsx", range = "A1:A10")
test = read_excel("Nth Fibonacci Number.xlsx", range = "B1:B10") %>%janitor::clean_names()
fib <- memoise(function(n) {
if (n <= 2) {
return(1)
} else {
return(fib(n - 1) + fib(n - 2))
}
})
result = input %>%
mutate(fib = map_dbl(input$N, fib)) %>%
select(fib)
identical(result$fib, test$answer_expected)
Solving the challenge of Find the Nth Fibonacci number with Excel VBA
Excel VBA solution 1 for Find the Nth Fibonacci number, proposed by Nicolas Micot:
Function f_fibonacci(N As Range)
Dim resultat As Variant, suite As Variant, tableau As Variant
Dim maxN
tableau = N.Value
maxN = WorksheetFunction.Max(N)
ReDim suite(0 To maxN)
ReDim resultat(1 To UBound(tableau, 1), 1 To 1)
suite(0) = 0
suite(1) = 1
For i = 2 To maxN
suite(i) = suite(i - 2) + suite(i - 1)
Next i
For i = 1 To UBound(resultat)
resultat(i, 1) = suite(tableau(i, 1))
Next i
f_fibonacci = resultat
End Function
&&&