Calculate Numerological Sum for a Name. a to i are allocated 1 to 9 sequentially. j to r are allocated 1 to 9 sequentially. s to z are allocated 1 to 8 sequentially. Hence for a name say “Julia”, the sum will be 1(j) + 3 (u) +3 (l) + 9 (i) + 1 (a) = 17 Numerological sum is calculated by summing up the digits till a single number is achieved. Hence, in this case, it would be 1+7 = 8 Say a sum is 999. Then Numerological sum will be 9+9+9 = 27 = 2+7 = 9
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 218
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Name Numerology with Power Query
Power Query solution 1 for Calculate Name Numerology, proposed by Omid Motamedisedeh:
let
Source = List.Transform(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Names],
each y(
List.Sum(
List.Transform(
Text.ToList(Text.Replace(Text.Lower(_), " ", "")),
(ox) => Number.Mod(List.PositionOf({"a" .. "z"}, ox), 9) + 1
)
)
)
),
y = (x) =>
if x < 10 then
x
else
y(
List.Sum(
List.Transform(
{1 .. Text.Length(Text.From(x))},
each Number.IntegerDivide(Number.Mod(x, Number.Power(10, (_))), Number.Power(10, _ - 1))
)
)
)
in
SourcePower Query solution 2 for Calculate Name Numerology, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Number.Mod(
List.Sum(List.Transform(Text.ToList([Names]), each Number.Mod(Character.ToNumber(_), 32))) - 1,
9
)
+ 1
)
in
AnsPower Query solution 3 for Calculate Name Numerology, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
L = (n) => Number.IntegerDivide(n, 10),
R = (n) => Number.Mod(n, 10),
S = Table.TransformRows(
Source,
each List.Last(
List.Generate(
() =>
List.Sum(
List.Transform(
Text.ToList(Text.Lower([Names])),
each Number.Mod(List.PositionOf({"a" .. "z"} & {" "}, _), 9) + 1
)
),
each L(_) > 0,
each L(_) + R(_),
each L(_) + R(_)
)
)
)
in
SPower Query solution 4 for Calculate Name Numerology, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Names], " "),
b = List.Combine(List.Transform(a, Text.ToList)),
c = List.Transform(b, Text.Lower),
d = List.Zip({{"a" .. "z"}, {1 .. 9, 1 .. 9, 1 .. 8}}),
e = Number.Mod(List.Sum(List.ReplaceMatchingItems(c, d)), 9)
in
if e = 0 then 9 else e
)
in
SolPower Query solution 5 for Calculate Name Numerology, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
sub = List.Zip({{"a" .. "z"}, {1 .. 9, 1 .. 9, 1 .. 8}}),
res = Table.AddColumn(
Fonte,
"Personaliza",
each [
a = List.Sum(
List.ReplaceMatchingItems(
List.Select(Text.ToList([Names]), each _ <> " "),
sub,
Comparer.OrdinalIgnoreCase
)
),
b = List.Transform(
{a},
each List.Sum(
List.Transform(
Text.ToList(Text.From(List.Sum(List.Transform(Text.ToList(Text.From(_)), Number.From)))),
Number.From
)
)
){0}
][b]
)
in
resSolving the challenge of Calculate Name Numerology with Excel
Excel solution 1 for Calculate Name Numerology, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(c,CODE(MID(a,SEQUENCE(LEN(a)),1)),MOD(SUM((c>64)*(MOD(MOD(c,32)-1,9)+1))-1,9)+1)))
Thank for محمد حلمي
=MAP(A2:A10,LAMBDA(a,MOD(SUM(MOD(CODE(MID(a,SEQUENCE(LEN(a)),1)),32))-1,9)+1))Excel solution 2 for Calculate Name Numerology, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(x,LET(t,LOWER(SUBSTITUTE(x," ","")),1+MOD(SUM(1+MOD(CODE(MID(t,SEQUENCE(LEN(t)),1))-97,9))-1,9))))Excel solution 3 for Calculate Name Numerology, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(c,CODE(UPPER(MID(x,SEQUENCE(LEN(x)),1))),1+MOD(SUM((c>64)*(c-64))-1,9))))
✅=MAP(A2:A10,LAMBDA(x,1+MOD(SUM(DECIMAL(MID(x,SEQUENCE(LEN(x)),1),36))-1,9)))Excel solution 4 for Calculate Name Numerology, proposed by محمد حلمي:
=MOD(MMULT(IFERROR(MOD(MOD(CODE(MID(A2:A10,
SEQUENCE(,30),1)),32)-1,9)+1,),SEQUENCE(30)^0)-1,9)Excel solution 5 for Calculate Name Numerology, proposed by محمد حلمي:
=MAP(SUBSTITUTE(UPPER(A2:A10)," ",""),LAMBDA(v,
REDUCE(MOD(CODE(MID(v,SEQUENCE(LEN(v)),1))-65,9)
+1,SEQUENCE(9),LAMBDA(a,d,SUM(MID(a,SEQUENCE(LEN(a)),1)+0)))))Excel solution 6 for Calculate Name Numerology, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(z,LET(a,CODE(UPPER(MID(z,SEQUENCE(LEN(z)),1))),b,MOD(a-64,9),REDUCE(SUM(IFS(a<65,"",b=0,9,1,b)),SEQUENCE(10),LAMBDA(x,y,SUM(--MID(x,SEQUENCE(LEN(x)),1)))))))Excel solution 7 for Calculate Name Numerology, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(N,LET(SP,LAMBDA(A,MID(A,SEQUENCE(LEN(A)),1)),SUM(--SP(SUM(--SP(SUM(MOD(CODE(SP(UPPER(SUBSTITUTE(N," ",""))))-65,9)+1))))))))Excel solution 8 for Calculate Name Numerology, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(A,LOWER(MID(z,SEQUENCE(LEN(z)),1)),K,CHAR(SEQUENCE(3,9,97)),B,TOROW(K),L,SEQUENCE(,9),D,TOROW(IF(L=K,,L)),E,CONCAT(MAP(A,LAMBDA(x,XLOOKUP(x,B,D,0)))),F,LAMBDA(me,N,IF(LEN(N)=1,N,me(me,SUM(1*MID(N,SEQUENCE(LEN(N)),1))))),F(F,E))))Excel solution 9 for Calculate Name Numerology, proposed by Hussein SATOUR:
=MAP(A2:A10, LAMBDA(x, LET(a, CHAR(ROW(97:122)), b, VSTACK(ROW(1:9), ROW(1:9), ROW(1:8)), n, MOD(SUM(XLOOKUP(MID(LOWER(x), SEQUENCE(LEN(x)), 1), a, b, 0)),9), IF(n=0,9,n))))Excel solution 10 for Calculate Name Numerology, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10, LAMBDA(a, MOD(SUM(MOD(TOROW(XMATCH(MID(LOWER(a),SEQUENCE(50),1),CHAR(SEQUENCE(26,,97))), 3)-1, 9)+1)-1, 9)+1))Excel solution 11 for Calculate Name Numerology, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(x,
LET(
_c1, CHAR(SEQUENCE(26, , CODE("a"))),
_c2, DROP(TOCOL(SEQUENCE(9) * SEQUENCE(, 3) ^ 0, , 1), -1),
_c3, MID(x, SEQUENCE(LEN(x)), 1),
_num, SUM(TOCOL(XLOOKUP(_c3, _c1, _c2), 3)),
REDUCE(_num, SEQUENCE(LEN(_num)), LAMBDA(a, v, SUM(--MID(a, SEQUENCE(LEN(a)), 1))))
)
)
)Excel solution 12 for Calculate Name Numerology, proposed by Asheesh Pahwa:
Let(
a,TOCOL(IFERROR(WRAPROWS(CHAR(SEQUENCE(26,,97)),9),""),1,TRUE),
b,CEILING(SEQUENCE(27,,1)/3,1),
c,MID(A2,SEQUENCE(LEN(A2)),1),
d,SUM(IFERROR(XLOOKUP(c,a,b),"")),
SUM(--MID(d,SEQUENCE(LEN(d)),1)))
Excel solution 13 for Calculate Name Numerology, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(MAP(MAP(A2:A10;LAMBDA(b;SUM(MAP(MID(b;SEQUENCE(LEN(b));1);LAMBDA(a;SUM(IF(ISNUMBER(SEARCH(a;TRANSPOSE(WRAPCOLS(CHAR(ROW(A97:A122));9;""));1));TOROW(SEQUENCE(9));"")))))));LAMBDA(q;SUM(VALUE(MID(q;SEQUENCE(LEN(q));1)))));LAMBDA(w;SUM(VALUE(MID(w;SEQUENCE(LEN(w));1)))))Excel solution 14 for Calculate Name Numerology, proposed by Pieter de Bruijn:
=MAP(SUBSTITUTE(LOWER(A2:A10)," ",""),LAMBDA(a,LET(b,CONCAT(MOD(CODE(MID(a,SEQUENCE(LEN(a)),1))-7,9)+1),REDUCE(b,SEQUENCE(LEN(b)),LAMBDA(x,y,SUM(--MID(x,SEQUENCE(LEN(x)),1)))))))Excel solution 15 for Calculate Name Numerology, proposed by Ziad A.:
=MAP(A2:A10,LAMBDA(a,LET(R,LAMBDA(R,n,IF(LEN(n)=1,n,R(R,SUM(--MID(n,SEQUENCE(LEN(n)),1))))),R(R,SUM(XLOOKUP(MID(a,SEQUENCE(LEN(a)),1),CHAR(ROW(65:90)),MOD(ROW(1:26)-1,9)+1,0))))))Excel solution 16 for Calculate Name Numerology, proposed by Quadri Olayinka Atharu:
=LET(_alpha,CHAR(SEQUENCE(26,,65)),
_val,DROP(TOCOL(SEQUENCE(,3,,0)*SEQUENCE(9),,1),-1),
_split,LAMBDA(_t,MID(_t,SEQUENCE(LEN(_t)),1)),
_splsum,LAMBDA(x,SUM(XLOOKUP(_split(x),_alpha,_val,0))),
_fsum,BYROW(A2:A10,LAMBDA(_n,_splsum(_n))),
MAP(_fsum,LAMBDA(x,LET(_ini,SUM(_split(x)+0),
IF(_ini>9,SUM(_split(_ini)+0),_ini)))))Excel solution 17 for Calculate Name Numerology, proposed by Henriette Hamer:
=MAP(MAP($A$2:$A$10;LAMBDA(a;LET(b;SUBSTITUTE(a;" ";"");SUM(MOD(CODE(UPPER(MID(b;SEQUENCE(1;LEN(b);1;1);1)))-65;9)+1))));LAMBDA(a;REDUCE(a;SEQUENCE(LEN(a));LAMBDA(a;v;SUM(MID(a;SEQUENCE(1;LEN(a);1;1);1)*1)))))Excel solution 18 for Calculate Name Numerology, proposed by Colin Davidson:
= x mod 9, where 0 maps to 9 (thanks, Simone Percy !). Again, valuing readability over brevity:
=LET(input, LOWER(SUBSTITUTE(A2, " ", "")),
digits, REDUCE(0, SEQUENCE(LEN(input)), LAMBDA(a,x, LET(
char, MID(input,x,1),
uni_val, UNICODE(char),
value, MOD(uni_val - 97, 9) +1,
a + value))
),
digits_mod, MOD(digits, 9),
IF(digits_mod = 0, 9, digits_mod)
)Solving the challenge of Calculate Name Numerology with Python in Excel
Python in Excel solution 1 for Calculate Name Numerology, proposed by Alejandro Campos:
def letter_to_number(letter):
letter = letter.lower()
if 'a' <= letter <= 'i':
return ord(letter) - ord('a') + 1
elif 'j' <= letter <= 'r':
return ord(letter) - ord('j') + 1
elif 's' <= letter <= 'z':
return ord(letter) - ord('s') + 1
else:
return 0
def numerological_sum(name):
initial_sum = sum(letter_to_number(letter) for letter in name)
while initial_sum >= 10:
initial_sum = sum(int(digit) for digit in str(initial_sum))
return initial_sum
names = xl("A2:A10")[0]
results = [numerological_sum(name) for name in names]
df = pd.DataFrame({'Numerological Sum': results})
df
&&&