List the numbers which are not a Fibonacci number.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 326
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List numbers not in Fibonacci sequence with Power Query
Power Query solution 1 for List numbers not in Fibonacci sequence, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
p = each
let
s = Number.Sqrt(_)
in
s <> Int64.From(s),
S = Table.SelectRows(
Source,
each
let
n = [Numbers]
in
p(5 * n * n - 4) and p(5 * n * n + 4)
)
in
SPower Query solution 2 for List numbers not in Fibonacci sequence, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Fibonacci = List.Accumulate({1..35}, {1}, (s, c)=> s&{List.Sum(List.LastN(s, 2))}),
Sol = List.Select(Source[Numbers], each not List.Contains(Fibonacci, _))
in
Sol
Con List.Generate
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Fibonacci = List.Generate(()=> [x=1, y=0],
each [x] <= List.Max(Source[Numbers]),
each [x = [y], y = [y] + [x]],
each [x]+[y]),
Sol = List.Select(Source[Numbers], each not List.Contains(Fibonacci, _))
in
Sol
Power Query solution 3 for List numbers not in Fibonacci sequence, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [
a = [Numbers],
b = List.Generate(
() => [Previus = 0, Current = 1],
each [Previus] + [Current] <= a,
each [Previus = [Previus] + [Current], Current = [Previus]],
each [Previus] + [Current]
),
c = not List.ContainsAny(b, {a})
][c]
)
in
resPower Query solution 4 for List numbers not in Fibonacci sequence, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddTestFib = Table.AddColumn(
Source,
"TestFib",
each Number.Mod(Number.Sqrt(5 * [Numbers] * [Numbers] + 4), 1)
* Number.Mod(Number.Sqrt(5 * [Numbers] * [Numbers] - 4), 1)
)
in
Table.SelectColumns(Table.SelectRows(AddTestFib, each [TestFib] <> 0), "Numbers")Power Query solution 5 for List numbers not in Fibonacci sequence, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = List.Generate(
() => [x = 0, y = 1],
each [x] + [y] <= 10000000,
each [y = [x] + [y], x = [y]],
each [x] + [y]
),
Sol = Table.FromList(
List.Difference(Origen[Numbers], a),
Splitter.SplitByNothing(),
{"Answer Expected"}
)
in
SolPower Query solution 6 for List numbers not in Fibonacci sequence, proposed by Luke Jarych:
let
MaxNumber = List.Max(Table1[Numbers]),
Fibonacci = List.Generate(
() => [x = 0, y = 1, counter = 0],
each [x] <= MaxNumber,
each [y = [x] + [y], x = [y], counter = if Number.IsEven(y) then [counter] + 1 else [counter]],
each [x]
),
FilterTable = Table.SelectRows(Table1, each not List.Contains(Fibonacci, [Numbers]))
in
FilterTableSolving the challenge of List numbers not in Fibonacci sequence with Excel
Excel solution 1 for List numbers not in Fibonacci sequence, proposed by Rick Rothstein:
=LET(
n,
5*A2:A10^2,
FILTER(
A2:A10,
ISNUMBER(
SEARCH(
".*.",
SQRT(
n+4
)&SQRT(
n-4
)
)
)
)
)Excel solution 2 for List numbers not in Fibonacci sequence, proposed by John V.:
=FILTER(A2:A10,
BYROW(MOD((5*A2:A10^2+{4,
-4})^0.5,
1),
AND)))Excel solution 3 for List numbers not in Fibonacci sequence, proposed by محمد حلمي:
=FILTER(A2:A10,ISNA(XMATCH(A2:A10,REDUCE({0;1},SEQUENCE(40),LAMBDA(a,d,VSTACK(a,SUM(TAKE(a,-2))))))))Excel solution 4 for List numbers not in Fibonacci sequence, proposed by Kris Jaganah:
=LET(a,
A2:A10,
b,
SEQUENCE(
,
100
),
c,
5^0.5,
d,
((((c+1)/2)^b)-(((-c+1)/2)^b))/c,
FILTER(a,
BYROW(--(a=d),
SUM)=0))Excel solution 5 for List numbers not in Fibonacci sequence, proposed by Julian Poeltl:
=LET(N,
A2:A10,
S,
SEQUENCE(
1000,
,
0
),
FB,
(1/SQRT(
5
))*(((1+SQRT(
5
))/2)^S-((1-SQRT(
5
))/2)^S),
IF,
BYROW(--(TRANSPOSE(
FB
)=N),
LAMBDA(
A,
SUM(
A
)
)),
FILTER(
N,
IF=0
))Excel solution 6 for List numbers not in Fibonacci sequence, proposed by Timothée BLIOT:
=LET(
A,
A2:A10,
B,
REDUCE(
{0;1},
SEQUENCE(
99
),
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
TAKE(
a,
-2
)
)
)
)
),
FILTER(
A,
MAP(
A,
LAMBDA(
x,
NOT(
ISNUMBER(
XMATCH(
x,
B
)
)
)
)
)
)
)Excel solution 7 for List numbers not in Fibonacci sequence, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
_num,
SQRT(
5 * x ^ 2 + {4,
-4}
),
NOT(
OR(
_num = INT(
_num
)
)
)
)
)
)
)Excel solution 8 for List numbers not in Fibonacci sequence, proposed by Sunny Baggu:
=LET(
_fs,
REDUCE(
1,
SEQUENCE(
100
),
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
TAKE(
a,
-2
)
)
)
)
),
FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
AND(
_fs <> a
)
)
)
)
)Excel solution 9 for List numbers not in Fibonacci sequence, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(MAP(A2:A10,
LAMBDA(n,
LET(x,
(5*n^2+{-4,
4})^0.5,
n/ NOT(
OR(
INT(
x
)=x
)
)))),
2)Excel solution 10 for List numbers not in Fibonacci sequence, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
A2:A10,
FILTER(n,
MOD((5*n^2+4)^0.5,
1)*MOD((5*n^2-4)^0.5,
1)))Excel solution 11 for List numbers not in Fibonacci sequence, proposed by LEONARD OCHEA 🇷🇴:
=FILTER(A2:A10,MMULT(N(MOD((5*A2:A10^2+{-4,4})^0.5,1)>0),{1;1})=2)Excel solution 12 for List numbers not in Fibonacci sequence, proposed by Bhavya Gupta:
=FILTER(A2:A10,BYROW(MOD(SQRT(5*A2:A10^2+{4,-4}),1),LAMBDA(x,AND(x))))Excel solution 13 for List numbers not in Fibonacci sequence, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A10,
sq,
SEQUENCE(
100
),
u,
REDUCE(
1,
sq,
LAMBDA(
x,
y,
LET(
a,
SUM(
TAKE(
x,
-2
)
),
b,
VSTACK(
x,
a
),
b
)
)
),
v,
FILTER(
z,
ISERROR(
XMATCH(
z,
u
)
)
),
v
)Excel solution 14 for List numbers not in Fibonacci sequence, proposed by Pieter de Bruijn:
=LET(f,REDUCE({0;1},SEQUENCE(34),LAMBDA(a,b,VSTACK(a,SUM(TAKE(a,-2))))),TOCOL(A2:A10/(1+MMULT(N(A2:A10<>TOROW(f)),f*0+1)-ROWS(f)),2))Excel solution 15 for List numbers not in Fibonacci sequence, proposed by Ziad A.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
a/AND(
MOD(
SQRT(
5*a^2+{4,
-4}
),
1
)
)
)
),
2
)Excel solution 16 for List numbers not in Fibonacci sequence, proposed by Ziad A.:
=LET(
F,
LAMBDA(
n,
MOD(
SQRT(
5*A2:A^2+n
),
1
)
),
FILTER(
A2:A,
F(
4
)*F(
-4
)
)
)Excel solution 17 for List numbers not in Fibonacci sequence, proposed by Giorgi Goderdzishvili:
=
LET(
nm,
A2:A10,
lst,
SCAN(
"0!1",
SEQUENCE(
60
),
LAMBDA(
a,
v,
CONCAT(
TAKE(
TEXTSPLIT(
a,
"!"
),
,
-1
),
"!",
SUM(
--TEXTSPLIT(
a,
"!"
)
)
)
)
),
tx,
1*TEXTBEFORE(
lst,
"!"
),
FILTER(
nm,
ISERROR(
MATCH(
nm,
tx,
0
)
)
)
)Excel solution 18 for List numbers not in Fibonacci sequence, proposed by Edwin Tisnado:
=TOCOL(MAP(A2:A10,LAMBDA(t,LET(a,SCAN(1,SEQUENCE(40),LAMBDA(x,y,ROUND(x*1.61803398874991,0))),VSTACK(IF(OR(t=a),,t))))),2)Excel solution 19 for List numbers not in Fibonacci sequence, proposed by Abdelrahman Omer, MBA, PMP:
=LET(A,A2:A10,B,REDUCE(1,SEQUENCE(100),LAMBDA(X,Y,VSTACK(X,SUM(TAKE(X,-2))))),FILTER(A,ISNA(XMATCH(A,B,0))))Excel solution 20 for List numbers not in Fibonacci sequence, proposed by Hazem Hassan:
=LET(
A,
A2:A10,
FILTER(
A,
ISNA(
MATCH(
A,
REDUCE(
{0; 1},
SEQUENCE(
60
),
LAMBDA(
X,
Y,
VSTACK(
X,
SUM(
TAKE(
X,
-2
)
)
)
)
),
0
)
)
)
)Solving the challenge of List numbers not in Fibonacci sequence with Python in Excel
Python in Excel solution 1 for List numbers not in Fibonacci sequence, proposed by John V.:
Hi everyone!
np.extract(((n + 4) ** 0.5 % 1) * ((n - 4) ** 0.5 % 1), i)
Blessings!
Solving the challenge of List numbers not in Fibonacci sequence with R
R solution 1 for List numbers not in Fibonacci sequence, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Non Fibonacci Numbers.xlsx", range = "A1:A10")
test = read_excel("Non Fibonacci Numbers.xlsx", range = "B1:B5")
generate_fibonacci_to_limit<- function(){
fib <- c(1, 2)
while (tail(fib, 1) < 1e7) {
fib <- c(fib, sum(tail(fib, 2)))
}
return(fib)
}
fib = generate_fibonacci_to_limit()
result = input %>%
mutate(is_fibonacci = ifelse(Numbers %in% fib, "Yes", "No")) %>%
filter(is_fibonacci == "No") %>%
select(Numbers)
&&