Square the individual digits of a number and sum them. Keep repeating this step again till the number reduces to a single digit. Work out the final single digit and number of iterations taken. Ex. 94 Iteration 1 = 9^2+4^2 = 97 Iteration 2 = 9^2+7^2 = 130 Iteration 3 = 1^2+3^2+0^2 = 10 Iteration 4 = 1^2+0^2 = 1 Hence answer is Final single digit = 1 and Number of iterations = 4
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 412
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Square Digits Until Single Number with Power Query
Power Query solution 1 for Square Digits Until Single Number, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
N = Number.From,
R = Table.AddColumn(S, "R", each
let
r = (n, i) => let s = List.Sum(List.Transform(Text.ToList(Text.From(n)), each N(_) * N(_))) in if s < 10 then {s, i} else @r(s, 1 + i)
in
Table.FromRows({r([Number], 1)}, {"Final Single Digit", "Number of Iterations"})
)[R]
in
Table.Combine(R)
Blessings!
Power Query solution 2 for Square Digits Until Single Number, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Number],
R = each
let
d = List.Sum(
List.Transform(
Text.ToList(Text.From(_{0})),
each
let
n = Number.From(_)
in
n * n
)
),
c = {d, _{1} + 1}
in
{@R(c), c}{Number.From(d < 10)},
S = Table.FromRows(
List.Transform(Source, each {_} & R({_, 0})),
{"Number", "Final Single Digit", "Number of Iterations"}
)
in
SPower Query solution 3 for Square Digits Until Single Number, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = [Number],
b = (x, y) =>
let
c = Text.From(x),
d = List.Transform(Text.ToList(c), Number.From),
e = List.Sum(List.Transform(d, each Number.Power(_, 2)))
in
if e < 10 then {e, y} else @b(e, y + 1),
f = b(a, 1)
in
Table.FromRows({f}, {"Final Single Digit", "Number of Iterations"})
)[A]
)
in
SolPower Query solution 4 for Square Digits Until Single Number, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
loop = (n, i) =>
[
a = List.Sum(
List.Transform(Text.ToList(Text.From(n)), (x) => Number.Power(Number.From(x), 2))
),
b = if a < 10 then {a, i} else @loop(a, 1 + i)
][b],
tab = Table.AddColumn(
Fonte,
"Personalizar",
each Table.FromRows({loop([Number], 1)}, {"Final", "N Iteração"})
),
res = Table.ExpandTableColumn(tab, "Personalizar", Table.ColumnNames(tab[Personalizar]{0}))
in
resPower Query solution 5 for Square Digits Until Single Number, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (x) =>
List.Sum(
List.Transform(
Text.ToList(Text.From(x)),
each let n = Number.FromText(_) in n * n
)
),
iter = (n) => if f(n) <= 9 then f(n) else @iterate(f(n)),
AddCol = Table.AddColumn(Source, "Final Digit", each iter([Number]))
in
AddCol
With Column C:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
f = (x) => List.Sum(List.Transform(Text.ToList(Text.From(x)), each let n = Number.FromText(_) in n * n )),
iterate = (L as list) => if f(L{0}) <= 9 then {f(L{0}), L{1}} else @iterate({f(L{0}), L{1} + 1}),
AddCol = Table.AddColumn(Source, "iterate", each Record.FromList(iterate({[Number], 1}), {"D", "i"})),
Expand = Table.ExpandRecordColumn(AddCol, "iterate", {"D", "i"}, {"Final Single Digit", "Number of Iterations"})
in
Expand
Power Query solution 6 for Square Digits Until Single Number, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
M = x,
N = if M < 10 then M * 10 else M,
G = List.Generate(
() => [i = 0, m = N],
each [m] >= 10,
each [
i = [i] + 1,
m =
let
a = Text.ToList(Text.From([m])),
b = List.Transform(a, Number.From),
c = List.Transform({0 .. List.Count(b) - 1}, each Number.Power(b{_}, 2)),
d = List.Sum(c)
in
d
]
),
e = List.Last(List.Transform(G, each Record.ToList(_))),
f = e{0} + 1,
g = Text.ToList(Text.From(e{1})),
h = List.Transform(g, Number.From),
i = List.Sum(List.Transform({0, 1}, each Number.Power(h{_}, 2))),
j = {i} & {f}
in
j,
k = Table.AddColumn(S, "Final Single Digit", each Fx([Number]){0}),
Sol = Table.AddColumn(k, "Number of Iterations", each Fx([Number]){1})
in
SolPower Query solution 7 for Square Digits Until Single Number, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
NP = Number.Power, LG = List.Generate, LL = List.Last,
PreAnw = Table.AddColumn(Source, "Lists", each
let
num = [Number],
FxNP = (x) =>
let
o = Text.From(x),
p = Text.ToList(o),
q = List.Transform(p, each NP(Number.From(_),2)),
r = List.Sum(q)
in
r,
FxSN = (y) =>
let
num2 = y,
b = LL( LG(
() => [i2 = 0, N1 = num2],
each [N1] < 100,
each [
i2 = [i2] + 1,
N1 = NP([N1],2)
]))
in
b,
NF = if num <= 9 then FxSN(num) else num,
Cicle = LG(
() => [i = if NF is number then 0 else NF[i2], N = if NF is number then num else NF[N1]],
each [N] >= 10,
each [
i = [i] + 1,
N = FxNP([N])]),
R = LL(Cicle),
a = {FxNP(R[N]), R[i] + 1}
in
a)[Lists]
in
Table.FromRows(PreAnw, {"Final Single Digit", "Number of Iterations"})
🧙♂️
Power Query solution 8 for Square Digits Until Single Number, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.Combine(
Table.AddColumn(
Source,
"sol",
each
let
s = [Number],
fct = (z) =>
Text.From(
List.Sum(List.Transform(Text.ToList(z), (x) => (Number.Power(Number.From(x), 2))))
),
b = List.Generate(
() => [x = fct(s)],
each Number.From([x]) >= 10,
each [x = fct([x])],
each [x]
),
c =
if Number.From(fct(s)) < 10 then
{{Number.From(fct(s))}, {1}}
else
{{Number.From(fct(List.Last(b)))}, {List.Count(b) + 1}}
in
Table.FromColumns(c, {"Final Single Digit", "Number of iterations"})
)[sol]
)
in
solPower Query solution 9 for Square Digits Until Single Number, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"c",
each List.Last(
List.Generate(
() => [
Final Single Digit = List.Sum(
List.Transform(Text.ToList(Text.From([Number])), (x) => Number.Power(Int64.From(x), 2))
),
c = Text.Length(Text.From(#"Final Single Digit")),
Number of Iterations = 1,
s = false
],
each not [s],
each [
Final Single Digit = List.Sum(
List.Transform(
Text.ToList(Text.From([#"Final Single Digit"])),
(x) => Number.Power(Int64.From(x), 2)
)
),
c = Text.Length(Text.From(#"Final Single Digit")),
Number of Iterations = [Number of Iterations] + 1,
s = [c] = 1
],
each Record.SelectFields(_, {"Final Single Digit", "Number of Iterations"})
)
)
),
#"Expanded c" = Table.ExpandRecordColumn(
#"Added Custom",
"c",
{"Final Single Digit", "Number of Iterations"},
{"Final Single Digit", "Number of Iterations"}
)
in
#"Expanded c"Solving the challenge of Square Digits Until Single Number with Excel
Excel solution 1 for Square Digits Until Single Number, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
B2:C2,
A3:A11,
LAMBDA(
a,
v,
LET(
r,
LAMBDA(
r,
n,
i,
LET(
b,
SUM(
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)^2
),
IF(
b<10,
HSTACK(
b,
i
),
r(
r,
b,
i+1
)
)
)
),
VSTACK(
a,
r(
r,
v,
1
)
)
)
)
)Excel solution 2 for Square Digits Until Single Number, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
B2:C2,
A3:A11,
LAMBDA(
c,
v,
VSTACK(
c,
REDUCE(
v,
SEQUENCE(
20
),
LAMBDA(
a,
n,
LET(
b,
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2
),
IF(
b<10,
HSTACK(
b,
n
),
IF(
COUNT(
a
)>1,
a,
b
)
)
)
)
)
)
)
)Excel solution 3 for Square Digits Until Single Number, proposed by Rick Rothstein:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A3:A11,
LAMBDA(
x,
LET(
s,
SEQUENCE(
25
),
n,
SCAN(
x,
s,
LAMBDA(
a,
x,
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2
)
)
),
TAKE(
FILTER(
n&" "&s,
LEN(
n
)=1
),
1
)
)
)
)
),
" ",
"|"
)Excel solution 4 for Square Digits Until Single Number, proposed by John V.:
=LET(r,LAMBDA(r,n,i,LET(s,SUM((0&MID(n,ROW(1:9),1))^2),IF(s<10,HSTACK(s,i),r(r,s,1+i)))),REDUCE(B2:C2,A3:A11,LAMBDA(a,v,VSTACK(a,r(r,v,1)))))
Or, with Bo Rydobon 🇹🇭 idea...
✅=LET(r,LAMBDA(r,n,i,LET(s,SUM((0&MID(n,ROW(1:9),1))^2),IF(s<10,s&i,r(r,s,1+i)))),--MID(MAP(A3:A11,LAMBDA(x,r(r,x,1))),{1,2},{1,4}))Excel solution 5 for Square Digits Until Single Number, proposed by محمد حلمي:
=LET(
r,
LAMBDA(
r,
a,
[v],
IF(
a<6,
HSTACK(
a,
v
),
r(
r,
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2
),
v+1
)
)
),
REDUCE(
B2:C2,
A3:A11,
LAMBDA(
a,
d,
VSTACK(
a,
r(
r,
d
)
)
)
)
)Excel solution 6 for Square Digits Until Single Number, proposed by محمد حلمي:
=LET(
r,
LAMBDA(
r,
a,
[v],
IF(
a<6,
HSTACK(
a,
v
),
r(
r,
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)^2
),
v+1
)
)
),
r(
r,
A3
)
)Excel solution 7 for Square Digits Until Single Number, proposed by Kris Jaganah:
=REDUCE(
{"Final Single Digit",
"Number of Iterations"},
A3:A11,
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
SEQUENCE(
20
),
b,
SCAN(
w,
a,
LAMBDA(
x,
y,
SUM(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)^2
)
)
),
TAKE(
FILTER(
HSTACK(
b,
a
),
LEN(
b
& )=1
),
1
)
)
)
)
)Excel solution 8 for Square Digits Until Single Number, proposed by Julian Poeltl:
=LET(F,
(SCAN(
A3,
SEQUENCE(
100
),
LAMBDA(
N,
S,
LET(
SS,
SUM(
MID(
N,
SEQUENCE(
1,
LEN(
N
)
),
1
)^2
),
IF(
AND(
N<10,
S>1
),
,
SS
)
)
)
)),
Z,
FILTER(
F,
F>0
),
HSTACK(
TAKE(
Z,
-1
),
COUNT(
Z
)
))Excel solution 9 for Square Digits Until Single Number, proposed by Julian Poeltl:
=HSTACK(MAP(A3:A11,
LAMBDA(A,
LET(F,
(SCAN(
A,
SEQUENCE(
100
),
LAMBDA(
N,
S,
LET(
SS,
SUM(
MID(
N,
SEQUENCE(
1,
LEN(
N
)
),
1
)^2
),
IF(
AND(
N<10,
S>1
),
,
SS
)
)
)
)),
Z,
FILTER(
F,
F>0
),
TAKE(
Z,
-1
)))),
MAP(A3:A11,
LAMBDA(A,
LET(F,
(SCAN(
A,
SEQUENCE(
100
),
LAMBDA(
N,
S,
LET(
SS,
SUM(
MID(
N,
SEQUENCE(
1,
LEN(
N
)
),
1
)^2
),
IF(
AND(
N<10,
S>1
),
,
SS
)
)
)
)),
Z,
FILTER(
F,
F>0
),
COUNT(
Z
)))))Excel solution 10 for Square Digits Until Single Number, proposed by Timothée BLIOT:
=REDUCE(
{"Final Single Digit",
"Number of Iterations"},
A3:A11,
LAMBDA(
y,
x,
LET(
A,
SCAN(
x,
ROW(
1:99
),
LAMBDA(
w,
v,
SUM(
MID(
w,
SEQUENCE(
LEN(
w
)
),
1
)^2
)
)
),
B,
XMATCH(
1,
LEN(
A
)
),
VSTACK(
y,
HSTACK(
INDEX(
A,
B
),
B
)
)
)
)
)Excel solution 11 for Square Digits Until Single Number, proposed by Hussein SATOUR:
=LET(
f,
LAMBDA(
z,
SCAN(
z,
SEQUENCE(
20
)^0,
LAMBDA(
x,
y,
SUMSQ(
--MID(
x*y,
SEQUENCE(
LEN(
x*y
)
),
)
)
)
)
),
HSTACK(
MAP(
A3:A11,
LAMBDA(
w,
INDEX(
f(
w
),
XMATCH(
1,
LEN(
f(
w
)
)
)
)
)
),
MAP(
A3:A11,
LAMBDA(
w,
XMATCH(
1,
LEN(
f(
w
)
)
)
)
)
)
)Excel solution 12 for Square Digits Until Single Number, proposed by Sunny Baggu:
=DROP(
REDUCE(
"",
A3:A11,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_s,
SEQUENCE(
20
),
_v,
MAP(
_s,
LAMBDA(
x,
REDUCE(
y,
SEQUENCE(
x
),
LAMBDA(
a,
v,
SUM(
POWER(
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
2
)
)
)
)
)
),
TAKE(
FILTER(
HSTACK(
_v,
_s
),
LEN(
_v
) = 1
),
1
)
)
)
)
),
1
)Excel solution 13 for Square Digits Until Single Number, proposed by LEONARD OCHEA 🇷🇴:
=LET(
F,
LAMBDA(
F,
x,
y,
LET(
s,
SUM(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)^2
),
IF(
s<10,
HSTACK(
s,
y
),
F(
F,
s,
y+1
)
)
)
),
REDUCE(
B2:C2,
A3:A11,
LAMBDA(
a,
b,
VSTACK(
a,
F(
F,
b,
1
)
)
)
)
)Excel solution 14 for Square Digits Until Single Number, proposed by Abdallah Ally:
=LET(a,
A3:A11,
f,
LAMBDA(f,
v,
n,
IF((LEN(
v
)=1)*(n>1),
HSTACK(
v,
n-1
),
f(
f,
SUM(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)^2
),
n+1
))),
DROP(
REDUCE(
"",
a,
LAMBDA(
x,
y,
VSTACK(
x,
f(
f,
y,
1
)
)
)
),
1
))Excel solution 15 for Square Digits Until Single Number, proposed by Charles Roldan:
=LET(
M,
LAMBDA(
g,
g(
g
)
),
SSQ,
M(LAMBDA(g,
LAMBDA(x,
IF(LEN(
x
),
LEFT(
x
) ^ 2 + g(
g
)(REPLACE(
x,
1,
1,
)),
)))),
Main,
M(LAMBDA(g,
LAMBDA(x,
[y],
IF((x < 10) * y,
HSTACK(
x,
y
),
g(
g
)(SSQ(
x
),
y + 1))))),
VLoop,
LAMBDA(f,
M(LAMBDA(g,
LAMBDA(x,
IF(COUNTA(
x
) = 1,
f(
x
),
VSTACK(f(
TAKE(
x,
1
)
),
g(
g
)(DROP(
x,
1
)))))))),
VLoop(
Main
)
)(A3:A10)Excel solution 16 for Square Digits Until Single Number, proposed by JvdV -:
=DROP(
REDUCE(
0,
A2:A11,
LAMBDA(
y,
z,
VSTACK(
y,
LET(
x,
LAMBDA(
f,
s,
n,
LET(
c,
SUM(
MID(
s,
SEQUENCE(
LEN(
s
)
),
1
)^2
),
IF(
c<10,
HSTACK(
c,
n+1
),
f(
f,
c,
n+1
)
)
)
),
x(
x,
z,
)
)
)
)
),
1
)Excel solution 17 for Square Digits Until Single Number, proposed by Ernesto Vega Castillo:
=DROP(
REDUCE(
0,
A3:A11,
LAMBDA(
a,
v,
LET(
r,
LAMBDA(
r,
n,
i,
LET(
b,
SUMPRODUCT(
POWER(
MID(
n,
ROW(
INDIRECT(
"1:" & LEN(
n
)
)
),
1
),
2
)
),
IF(
b < 10,
HSTACK(
b,
i
),
r(
r,
b,
i + 1
)
)
)
),
VSTACK(
a,
r(
r,
v,
1
)
)
)
)
),
1
)Solving the challenge of Square Digits Until Single Number with Python
Python solution 1 for Square Digits Until Single Number, proposed by Cristobal Salcedo Beltran:
https://github.com/cristobalsalcedo90/BI_Challenges
code
import pandas as pd
excel_file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_412 - Square Sum Iterate till a Single Digit .xlsx"
pandas_df = pd.read_excel(excel_file_path, usecols=[0], header=1)
def calculate_final_digit_and_iterations(number):
iterations = 0
number = sum(int(digit) ** 2 for digit in str(number))
if number < 10:
return pd.Series([number, 1])
while number >= 10:
number = sum(int(digit) ** 2 for digit in str(number))
iterations += 1
return pd.Series([number, iterations + 1])
pandas_df[['Final Single Digit', 'Number of Iterations']] = pandas_df.apply(
lambda row: calculate_final_digit_and_iterations(row.iloc[0]), axis=1)
print(pandas_df)
Solving the challenge of Square Digits Until Single Number with Python in Excel
Python in Excel solution 1 for Square Digits Until Single Number, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_412 - Square Sum Iterate till a Single Digit .xlsx'
df = pd.read_excel(file_path, usecols='A', skiprows=1)
df_test = pd.read_excel(file_path, usecols='A:C', skiprows=1)
def single_digit(number):
value, n = sum([int(x) ** 2 for x in str(number)]), 1
while len(str(value)) != 1:
value = sum([int(x) ** 2 for x in str(value)])
n += 1
return value, n
df[['Final Single Digit', 'Number of Iterations']] = df['Number'].apply(single_digit).tolist()
print(f'Original results:n{df_test}nnMy Results:n{df}')
Python in Excel solution 2 for Square Digits Until Single Number, proposed by Giorgi& Goderdzishvili:
lst = xl("A2:A11", headers=True).Number.to_list()
fin = [["Final Single Digit","Number of Iterations"]]
for el in lst:
counter = 0
while True:
counter+=1
el = sum([int(i)**2 for i in str(el)])
if len(str(el))==1:
fin.append([el,counter])
break
fin
Solving the challenge of Square Digits Until Single Number with R
R solution 1 for Square Digits Until Single Number, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/412 Square Sum Iterate till a Single Digit .xlsx", range = "A2:A11")
test = read_excel("Excel/412 Square Sum Iterate till a Single Digit .xlsx", range = "B2:C11")
sum_square <- function(x) {
iter <- 0
if (nchar(x) == 1) {
y <- x^2
iter <- iter + 1
} else {
y <- x
}
while (nchar(y) > 1) {
digits <- as.numeric(strsplit(as.character(y), "")[[1]])
y <- sum(digits^2)
iter <- iter + 1
}
return(c(iter, y))
}
result = input %>%
mutate( r = map(Number, sum_square),
FSD = map_dbl(r, 2),
Iterations = map_dbl(r, 1)) %>%
select(-c(r, Number))
&