List all Pronic numbers. A Pronic number is that number which is a result of product of two consecutive integers. For ex. 506 which is the result of 22 & 23
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 276
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List All Pronic Numbers with Power Query
Power Query solution 1 for List All Pronic Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each
let
q = Number.RoundDown(Number.Sqrt([Numbers]), 0)
in
[Numbers] = q * q + q
)
in
AnsPower Query solution 2 for List All Pronic Numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Numbers],
S = List.Select(
Source,
(n) => List.Last(List.Generate(() => 1, each (_ - 1) * _ < n, each _ + 1, each _ * (_ + 1) = n))
)
in
SPower Query solution 3 for List All Pronic Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [SR = Number.RoundDown(Number.Sqrt([Numbers])), R = SR * (SR + 1) = [Numbers]][R]
)
in
ReturnPower Query solution 4 for List All Pronic Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.SelectRows(Source, (x)=>
let
n = Number.RoundDown(Number.Sqrt(x[Numbers])),
a = List.AllTrue(List.Transform({n..n+1}, each Number.Mod(x[Numbers], _)=0))
in a)
in
Sol
2
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.SelectRows(Source, (x)=>
let
a = {1..Number.RoundDown(Number.Sqrt(x[Numbers]))},
b = List.Transform(a, each {_} & {x[Numbers]/_}),
c = List.Select(b, each Number.Abs(_{0}-_{1})=1){0}? <> null
in c)
in
Sol
Power Query solution 5 for List All Pronic Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [a = Number.RoundDown(Number.Sqrt([Numbers]), 0), b = [Numbers] = a * (a + 1)][b]
)
in
resPower Query solution 6 for List All Pronic Numbers, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddPronic = Table.SelectRows(
Table.AddColumn(
Source,
"Pronic",
each [
a = Number.RoundDown(Number.Sqrt([Numbers])),
b = if (a * (a + 1)) = [Numbers] then [Numbers] else null
][b]
),
each [Pronic] <> null
)
in
AddPronicPower Query solution 7 for List All Pronic Numbers, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.SelectRows(
Source,
each
let
a = [Numbers],
b = Number.RoundDown(Number.Sqrt(a)),
c = b + 1,
d = b * c
in
a = d
)
in
ResultPower Query solution 8 for List All Pronic Numbers, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "Numbers"]}[Content],
ChangeType = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}}),
// Custom Function to check if a number is a pronic number
IsPronic = (input as number) =>
let
// Calculate the square root of the number
Square_Root = Number.Sqrt(input),
// Convert the square root to an integer
RoundDown = Number.RoundDown(Square_Root),
// Calculate the product of two consecutive integers
Product = RoundDown * (RoundDown + 1)
in
input = Product,
// Use custom function to select all Pronic Numbers
SelectPronicNumbers = Table.SelectRows(ChangeType, each IsPronic([Numbers]))
in
SelectPronicNumbersPower Query solution 9 for List All Pronic Numbers, proposed by Kalyan Kumar Reddy Kethireddy:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(#"PronicNumbers", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Numbers = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}}),
#"Pronic Numbers" = Table.SelectRows(
#"Changed Type",
each [
a = Number.RoundDown(Number.Sqrt([Numbers]), 0),
b = List.Product({a .. a + 1}),
c = [Numbers] = b
][c]
)
in
#"Pronic Numbers"Power Query solution 10 for List All Pronic Numbers, proposed by Ian Segard:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}}),
#"Inserted Square Root" = Table.AddColumn(
#"Changed Type",
"Square Root",
each Number.Sqrt([Numbers]),
type number
),
#"Rounded Down" = Table.TransformColumns(
#"Inserted Square Root",
{{"Square Root", Number.RoundDown, Int64.Type}}
),
#"Inserted Addition" = Table.AddColumn(
#"Rounded Down",
"Addition",
each [Square Root] + 1,
type number
),
#"Inserted Multiplication" = Table.AddColumn(
#"Inserted Addition",
"Multiplication",
each [Square Root] * [Addition],
type number
),
#"Added Conditional Column" = Table.AddColumn(
#"Inserted Multiplication",
"Pronic",
each if [Numbers] = [Multiplication] then true else false
),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Pronic] = true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Numbers"})
in
#"Removed Other Columns"Solving the challenge of List All Pronic Numbers with Excel
Excel solution 1 for List All Pronic Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(a,
A2:A10,
q,
INT(
a^0.5
),
TOCOL(a/(a/q=q+1),
3))Excel solution 2 for List All Pronic Numbers, proposed by Rick Rothstein:
=LET(
a,
A2:A10,
i,
INT(
a^0.5
),
FILTER(
a,
i+i*i=a
)
)Excel solution 3 for List All Pronic Numbers, proposed by John V.:
=LET(n,
A2:A10,
i,
INT(
n^0.5
),
TOCOL(n/(n=i^2+i),
2))Excel solution 4 for List All Pronic Numbers, proposed by محمد حلمي:
=LET(a,A2:A10,i,(a*4+1)^0.5,TOCOL(a/(i=INT(i)),2))Excel solution 5 for List All Pronic Numbers, proposed by محمد حلمي:
=LET(a,
A2:A10,
s,
SEQUENCE(
,
9000
),
TOCOL(a/(a=s*s-s),
2))Excel solution 6 for List All Pronic Numbers, proposed by Kris Jaganah:
=LET(a,
A2:A10,
b,
ROUNDUP(
a^0.5,
0
),
TOCOL(a/(b*(b-1)=a),
3))Excel solution 7 for List All Pronic Numbers, proposed by Timothée BLIOT:
=LET(A,
A2:A10,
B,
ROUNDUP(
A^0.5,
0
),
FILTER(A,
B*(B-1)=A))Excel solution 8 for List All Pronic Numbers, proposed by Oscar Mendez Roca Farell:
=LET(
_s,
SEQUENCE(
10^4
),
TOCOL(
XLOOKUP(
DROP(
_s,
-1
)*DROP(
_s,
1
),
A2:A10,
A2:A10
),
2
)
)Excel solution 9 for List All Pronic Numbers, proposed by Sunny Baggu:
=TOCOL(
A2:A10 * 1 /
(A2:A10 = MAP(A2:A10,
LAMBDA(x,
LET(_a,
INT(
SQRT(
x
)
),
_a * (_a + 1))))),
3
)Excel solution 10 for List All Pronic Numbers, proposed by LEONARD OCHEA 🇷🇴:
=LET(
n,
A2:A10,
a,
INT(
n^0.5
),
b,
a+1,
FILTER(
n,
n=a*b
)
)
=LET(
n,
A2:A10,
a,
INT(
n^0.5
),
FILTER(
n,
n=a^2+a
)
)Excel solution 11 for List All Pronic Numbers, proposed by Abdallah Ally:
=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(v,LET(a,v,b,SEQUENCE(ROUNDUP(SQRT(v),0)+1),REDUCE(FALSE,b,LAMBDA(x,y,OR(x,y*(y+1)=a)))))))Excel solution 12 for List All Pronic Numbers, proposed by Anshu Bantra:
=LET(
lst,
A2:A10,
srt,
MAP(
lst,
LET(
comp,
LAMBDA(num,
LET(
seq,
SEQUENCE(
SQRT(
num
)
),
prod,
MAP(seq,
LAMBDA(s,
s * (s + 1))),
SUM(--(prod = num))
)
),
comp
)
),
FILTER(
lst,
srt
)
)Excel solution 13 for List All Pronic Numbers, proposed by Charles Roldan:
=LAMBDA(
x,
FILTER(
x,
NOT(
MOD(
SQRT(
4*x+1
),
1
)
)
)
)(A2:A10)Excel solution 14 for List All Pronic Numbers, proposed by Julien Lacaze:
=LET(data,
A2:A10,
max,
INT(
SQRT(
MAX(
data
)
)
),
v,
SEQUENCE(
max+1
),
FILTER(data,
MAP(data,
LAMBDA(d,
OR(d=(v*v-v))))))Excel solution 15 for List All Pronic Numbers, proposed by Pieter de Bruijn:
=LET(a,
A2:A10,
r,
SQRT(
a
),
I,
INT(
r
),
FILTER(a,
I*(I+1)=a))
or in one go: =LET(a,
A2:A10,
i,
INT(
SQRT(
a
)
),
FILTER(a,
i*(i+1)=a))Excel solution 16 for List All Pronic Numbers, proposed by Nicolas Micot:
=LET(_nombres;A4:A12;
_estPronic;LAMBDA(l_nombre;
LET(_racine;RACINE(l_nombre);
_n1;ARRONDI.INF(_racine;0);
_n2;SI(_n1=_racine;_n1-1;_n1+1);
_n1*_n2=l_nombre));
FILTRE(_nombres;_estPronic(_nombres)))Excel solution 17 for List All Pronic Numbers, proposed by Ziad A.:
=FILTER(
A2:A,
LET(
s,
INT(
A2:A^0.5
),
A2:A=s*s+s
)
)Excel solution 18 for List All Pronic Numbers, proposed by Giorgi Goderdzishvili:
=TOCOL(MAP(A2:A10,LAMBDA(x,
LET(
nm,x,
sqr, ROUNDDOWN(nm^(1/2),0),
chck, nm=(sqr*(sqr+1)),
x/chck))),3)Excel solution 19 for List All Pronic Numbers, proposed by Daniel Garzia:
=LET(r,ROW(1:9999),TOCOL(MAP(A2:A10,LAMBDA(x,x/ISNUMBER(XMATCH(x,r*(r-1))))),2))Excel solution 20 for List All Pronic Numbers, proposed by samir tobeil:
=LET(x,
A2:A10,
s,
MOD((1+(1+(4*x))^0.5)/2,
1)=0,
FILTER(
x,
s
))
OR
=TOCOL(LET(s,
SEQUENCE(
999999
),
XLOOKUP(A2:A10,
s*(s+1),
s*(s+1))),
2)
OR
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(s,
ROW(
1:99999
),
FILTER(s*(s-1),
(s*(s-1)=x))))),
2)Excel solution 21 for List All Pronic Numbers, proposed by Md Ismail Hosen:
=LET(Numbers, A2:A10, fxOne, LAMBDA(Number, LET(Seq, SEQUENCE(SQRT(Number)), OR((Seq * (Seq + 1)) = Number))), Result, FILTER(Numbers, MAP(Numbers, fxOne)), Result)Excel solution 22 for List All Pronic Numbers, proposed by Mungunbayar Bat-Ochir:
=LET(
nums;
A2:A10;
FILTER(nums;
INT(
SQRT(
nums
)
)*(INT(
SQRT(
nums
)
)+1)=nums)
)Excel solution 23 for List All Pronic Numbers, proposed by Hazem Hassan:
=LET(
x,
A2:A10,
r,
SQRT(
x
)+0.5,
FILTER(
x,
ROUNDUP(
r,
0
)*ROUNDDOWN(
r,
0
)=x
)
)Excel solution 24 for List All Pronic Numbers, proposed by Hazem Hassan:
=LET(e,A2:A10,s,SEQUENCE(100^2),TOCOL(XMATCH(e,s*(s+1),0)^0*e,3))Excel solution 25 for List All Pronic Numbers, proposed by Kriddakorn Pongthanisorn:
=LET(_num,
A2:A10,
_sqrt,
BYROW(_num ,
LAMBDA(_num,
(FLOOR(
SQRT(
_num
),
1
)*(FLOOR(
SQRT(
_num
),
1
)+1)))),
_ver,
ARRAYFORMULA(
_sqrt=_num
),
_pronic,
CHOOSECOLS(
FILTER(
HSTACK(
_sqrt,
_ver
),
_ver=TRUE
),
1
),
_pronic)Excel solution 26 for List All Pronic Numbers, proposed by Jeff Blakley:
=FILTER(
A2:A10,
A2:A10=BYROW(
INT(
SQRT(
A2:A10
)
)+{0,
1},
LAMBDA(
rw,
PRODUCT(
rw
)
)
)
)Excel solution 27 for List All Pronic Numbers, proposed by Deepak Dalal:
= FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(a,
LET(rt,
ROUND(
SQRT(
a
),
0
),
IF(OR(rt * (rt - 1) = a,
rt * (rt+1) = a),
1,
0)))))Excel solution 28 for List All Pronic Numbers, proposed by Ali Hassan, CPA:
=TOCOL(
BYROW(
A2:A10,
LAMBDA(
row,
LET(
array,
row/SEQUENCE(
SQRT(
row
)
),
factors,
FILTER(
array,
ROUND(
array,
0
)=array,
),
IF(
MATCH(
1,
factors-row/factors,
0
)>0,
row,
""
)
)
)
),
3
)Solving the challenge of List All Pronic Numbers with Python
Python solution 1 for List All Pronic Numbers, proposed by Anshu Bantra:
import numpy as np
def pronic(lst):
pronic_lst = []
for num in lst:
for i in range(1,int(np.sqrt(num))+1):
if i*(i+1) == num:
pronic_lst.append(num)
break
return pronic_lst
pronic(lst.Numbers)
Solving the challenge of List All Pronic Numbers with Python in Excel
Python in Excel solution 1 for List All Pronic Numbers, proposed by Bo Rydobon 🇹🇭:
Python
df = xl("A2:A10")[0].values
df[df/(df**0.5//1)==df**0.5//1+1]Python in Excel solution 2 for List All Pronic Numbers, proposed by Hussein SATOUR:
=LET(n,A2:A10,FILTER(n,MOD(n,INT(SQRT(n))+1)=0))
Python :
import math; n = xl("A1:A10", headers=True); n['X'] = n.Numbers.apply(lambda x: x % (math.floor(x**0.5)+1)); n.query('X == 0')['Numbers']
Python in Excel solution 3 for List All Pronic Numbers, proposed by Diarmuid Early:
Python one-liner:
[i for i in xl("A2:A10").values if (4*i+1)**0.5 == int((4*i+1)**0.5)]
If a = b(b+1) then 4a+1 is a perfect square: 4b^2 + 4b + 1 = (2b+1)^2
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder
Solving the challenge of List All Pronic Numbers with R
R solution 1 for List All Pronic Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Pronic Numbers.xlsx") %>% select(Numbers)
is_pronic = function(n) {
l_number = floor(sqrt(n))
result = n == l_number * (l_number+1)
return(result)
}
result = input %>%
mutate(IsPronic = map(Numbers, is_pronic)) %>%
filter(IsPronic == TRUE) %>%
select(Numbers)
Solving the challenge of List All Pronic Numbers with Excel VBA
Excel VBA solution 1 for List All Pronic Numbers, proposed by Vasin Nilyok:
VBA
Sub PronicNumbers()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
rAns = 2
For r = 2 To LastRow
QNum = Cells(r, 1)
TargetNum = Fix(Sqr(QNum))
For i = 1 To TargetNum
If QNum = i * (i + 1) Then
Cells(rAns, 3) = QNum
rAns = rAns + 1
End If
Next i
Next r
End Sub
Solving the challenge of List All Pronic Numbers with DAX
DAX solution 1 for List All Pronic Numbers, proposed by Szabolcs Phraner:
Pronic Numbers:=
SUMX(
FILTER('Numbers_DAX';
INT(SQRT('Numbers_DAX'[Numbers])) * (INT(SQRT('Numbers_DAX'[Numbers])) + 1) = 'Numbers_DAX'[Numbers]
);
'Numbers_DAX'[Numbers]
)
&&