Calculate Digital root of the given numbers. This is also known as Digital Sum, Seed Number, Numerological Sum. Sum all digits such that final sum reduces to a single digit. 25 = 2+5 = 7 59 = 5+9 = 14 = 1+4 = 5 5638 = 5+6+3+8 = 22 = 2+2 = 4.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 121
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Digital Root with Power Query
Power Query solution 1 for Calculate Digital Root, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs = Table.TransformRows(
Source,
each List.Accumulate(
{1, 1, 1},
[Number],
(s, l) => List.Sum(List.Transform(Text.ToList(Text.From(s)), Number.From))
)
)
in
RsPower Query solution 2 for Calculate Digital Root, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
Result = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.ToList([Number]),
b = List.Transform(a, Number.From),
c = List.Sum(b),
d = List.Sum(List.Transform(Text.ToList(Text.From(c)), Number.From)),
e = if d > 10 then d - 9 else d
][e]
)
in
ResultPower Query solution 3 for Calculate Digital Root, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddIdx = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
AddListGen = Table.AddColumn(
AddIdx,
"Custom",
each List.Generate(
() => [
x = List.Sum(List.Transform(Text.ToList(Text.From([Number])), Number.From)),
y = List.Count(List.Transform(Text.ToList(Text.From([Number])), Number.From))
],
each [y] > 1,
each [
x = List.Sum(List.Transform(Text.ToList(Text.From([x])), Number.From)),
y = List.Count(List.Transform(Text.ToList(Text.From([x])), Number.From))
]
)
),
Expand1 = Table.ExpandListColumn(AddListGen, "Custom"),
Expand2 = Table.ExpandRecordColumn(Expand1, "Custom", {"x"}, {"Custom.x"}),
Group = Table.Group(Expand2, {"Number"}, {{"Answer", each List.Min([Custom.x]), type number}})
in
GroupPower Query solution 4 for Calculate Digital Root, proposed by Brian Julius:
Power Query solution 5 for Calculate Digital Root, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fn = (n as text) =>
List.Last(
List.Generate(
() => [x = n, y = Text.Length(n)],
each [y] > 1,
each [
x = Text.From(List.Sum(List.Transform(Text.ToList([x]), Number.From))),
y = Text.Length([x])
],
each Number.From([x])
)
),
Final = Table.TransformColumns(Source, {{"Number", Fn}})
in
FinalPower Query solution 6 for Calculate Digital Root, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "DigitalRoot"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Digital Root",
each List.Last(
List.Generate(
() => [
n = List.Sum(List.Transform(Text.ToList([Number]), Number.FromText)),
i = Text.Length(Text.From(n))
],
each [i] > 1,
each [
n = List.Sum(List.Transform(Text.ToList(Text.From([n])), Number.FromText)),
i = Text.Length(Text.From([n]))
],
each [n]
)
)
)
in
#"Added Custom"Power Query solution 7 for Calculate Digital Root, proposed by Venkata Rajesh:
let
Source = Data,
Result = Table.AddColumn(
Source,
"Answer",
each List.Accumulate(
List.Transform(Text.ToList([Number]), each Number.From(_)),
0,
(state, current) =>
let
_num = state + current
in
if _num > 9 then
List.Sum(List.Transform(Text.ToList(Text.From(_num)), each Number.From(_)))
else
_num
),
Int64.Type
)
in
ResultPower Query solution 8 for Calculate Digital Root, proposed by Jan Willem Van Holst:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PY3BDcAwCAN3ybsPih0Ms0Tdf42SqCofY1vyrTXKOGNcI8ZzrYGCwby9H59VNCKN2RlOdneictEmOkcXOoWKspDNfiAmxYl/qoK3W7J2rS2+tfv6UMGGQb3uBdU+BcKzMR/leQE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t, Answer = _t]
),
dx = (n) =>
if Text.Length(n) = 1 then
n
else
@dx(Text.From(List.Sum(List.Transform(Text.ToList((n)), Number.From)))),
answer = Table.AddColumn(Source, "MyAnswer", each dx([Number]))
in
answerSolving the challenge of Calculate Digital Root with Excel
Excel solution 1 for Calculate Digital Root, proposed by Bo Rydobon 🇹🇭:
=MOD(MMULT(--(0&MID(
A2:A8,
{1,
13,
25},
12
)),
{1;1;1})-1,
9)+1
=MOD(
MMULT(
IFERROR(
--MID(
A2:A8,
{1,
13,
25},
12
),
),
{1;1;1}
)-1,
9
)Excel solution 2 for Calculate Digital Root, proposed by Rick Rothstein:
=MAP(A2:A8,LAMBDA(x,MOD(SUM(0+MID(x,SEQUENCE(LEN(x)),1))-1,9)+1))
Edit Note: The above formula does not take into account a value of 0 being in one of the cells. If that could be a possibility, then this formula should be used instead...
=MAP(A2:A8,LAMBDA(x,(MOD(SUM(0+MID(x,SEQUENCE(LEN(x)),1))-1,9)+1)*(x>0)))Excel solution 3 for Calculate Digital Root, proposed by John V.:
=MAP(A2:A8,
LAMBDA(x,
1+MOD(SUM(--(0&MID(
x,
ROW(
1:99
),
1
)))-1,
9)))Excel solution 4 for Calculate Digital Root, proposed by محمد حلمي:
=MAP(
A2:A8,
LAMBDA(
a,
REDUCE(
a,
SEQUENCE(
9
),
LAMBDA(
a,
d,
SUM(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)+0
)
)
)
)
)Excel solution 5 for Calculate Digital Root, proposed by Kris Jaganah:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
x,
b,
SUM(
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)/1
),
c,
SUM(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
)/1
),
d,
SUM(
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
)/1
),
d
)
)
)Excel solution 6 for Calculate Digital Root, proposed by Julian Poeltl:
=MAP(
A2:A8,
LAMBDA(
N,
LET(
S,
LAMBDA(
A,
SUM(
--MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
),
REDUCE(
N,
SEQUENCE(
10
),
LAMBDA(
A,
B,
S(
A
)
)
)
)
)
)Excel solution 7 for Calculate Digital Root, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_e,
LAMBDA(
ME,
a,
IF(
a < 10,
a,
ME(
ME,
SUM(
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)
)
),
_r,
MAP(
A2:A8,
LAMBDA(
x,
_e(
_e,
x
)
)
),
_r
)Excel solution 8 for Calculate Digital Root, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A8,
LAMBDA(
a,
REDUCE(
a,
SEQUENCE(
LEN(
a
)
),
LAMBDA(
x,
y,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
)Excel solution 9 for Calculate Digital Root, proposed by Timothée BLIOT:
=LET(
F,
LAMBDA(
Self,
n,
IF(
LEN(
n
)=1,
n,
Self(
Self,
SUM(
--MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)
)
)
)
),
MAP(
A2:A8,
LAMBDA(
a,
F(
F,
a
)
)
)
)Excel solution 10 for Calculate Digital Root, proposed by Hussein SATOUR:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
LAMBDA(
x,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
),
f,
LAMBDA(
ME,
y,
IF(
LEN(
y
) = 1,
y,
ME(
ME,
a(
y
)
)
)
),
f(
f,
x
)
)
)
)Excel solution 11 for Calculate Digital Root, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
MOD(
A2-1,
9
)+1Excel solution 12 for Calculate Digital Root, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
b;
ROW(
$A$1:$A$100
);
a;
SUM(
IFERROR(
VALUE(
MID(
SUM(
VALUE(
MID(
A2;
ROW(
INDIRECT(
"A1:"&"A"&LEN(
A2
)
)
);
1
)
)
);
b;
1
)
);
""
)
);
IF(
LEN(
a
)>1;
SUM(
IFERROR(
VALUE(
MID(
a;
b;
1
)
);
""
)
);
a
)
)Excel solution 13 for Calculate Digital Root, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAP(
A2:A8,
LAMBDA(
x,
LET(
Dg,
LAMBDA(
ME,
x,
IF(
LEN(
x
)=1,
x,
ME(
ME,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
),
Dg(
Dg,
x
)
)
)
)Excel solution 14 for Calculate Digital Root, proposed by Guillermo Arroyo&:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
f,
LAMBDA(
p,
s,
LET(
q,
SUM(
--MID(
s,
SEQUENCE(
LEN(
s
)
),
1
)
),
IF(
LEN(
q
)=1,
q,
p(
p,
q
)
)
)
),
f(
f,
a
)
)
)
)
=MAP(A2:A8,
LAMBDA(a,
LET(b,
SUM(--(0&MID(
a,
{1,
11,
21,
31},
10
))),
IF(
b=0,
0,
MOD(
b-1,
9
)+1
))))Excel solution 15 for Calculate Digital Root, proposed by Meni Porat:
=LET(
x,
SUM(
IFERROR(
--MID(
B1,
SEQUENCE(
LEN(
B1
)
),
1
),
0
)
),
y,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
IF(
LEN(
x
)=1,
x,
IF(
y>9,
y-9,
y
)
)
)Excel solution 16 for Calculate Digital Root, proposed by Tushar Mehta:
=LAMBDA(
rng,
LET(
_name,
"digital root",
_ref,
"https://en.wikipedia.org/wiki/Digital_root",
MAP(
rng,
LAMBDA(
x,
LET(
_doc,
"sumOnce reduces the number of digits to under 14",
_sumOnce,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
base,
10,
IF(
_sumOnce=0,
0,
MOD(
_sumOnce-1,
base-1
)+1
)
)
)
)
)
)(A2:A9)Solving the challenge of Calculate Digital Root with Python in Excel
Python in Excel solution 1 for Calculate Digital Root, proposed by Alejandro Campos:
def digital_root(n):
while n >= 10:
n = sum(int(digit) for digit in str(n))
return n
numbers = xl("A2:A8")[0]
results = {'Number': numbers, 'Digital Root': [digital_root(int(num)) for num in numbers]}
df = pd.DataFrame(results)
df
Solving the challenge of Calculate Digital Root with SQL
SQL solution 1 for Calculate Digital Root, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDERING
,D.NUMBER
,CAST(D.NUMBER AS VARCHAR(MAX)) AS TEMP
,0 AS ANSWER
FROM DATA D
),
CALC
AS
(
SELECT
DP.ORDERING, DP.NUMBER, DP.TEMP, DP.ANSWER
FROM DATA_PREP DP
UNION ALL
SELECT
C.ORDERING
,C.NUMBER
,CASE
THEN CAST(C.ANSWER AS VARCHAR(MAX))
ELSE SUBSTRING(C.TEMP, 1, LEN(C.TEMP) - 1)
END AS TEMP
,CASE
THEN 0
ELSE C.ANSWER + SUBSTRING(C.TEMP, LEN(C.TEMP), 1)
END AS ANSWER
FROM CALC C
NOT(C.TEMP = '' AND C.ANSWER < 10)
)
SELECT
C.NUMBER, C.ANSWER
FROM CALC C
WHERE
C.TEMP = ''
AND C.ANSWER < 10
ORDER BY
C.ORDERING
;
&&