Find the count and sum of Palindrome numbers for N digits Palindrome numbers. A Palindrome number is that which is same even when read from backwards. Such as 5665. For N = 2, palindrome numbers are 11, 22, 33, 44, 55, 66, 77, 88, 99. Hence, count is 9 and sum is 495.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 495
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Palindrome Count and Sum by Digit with Power Query
Power Query solution 1 for Palindrome Count and Sum by Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Pros = Table.AddColumn(
Origen,
"A",
(x) =>
let
a = {Number.Power(10, x[N] - 1) + 1 .. Number.Power(10, x[N]) - 1},
b = List.Transform(a, Text.From),
c = List.Select(
b,
each
if Number.IsEven(x[N]) then
Text.Start(_, x[N] / 2) = Text.Reverse(Text.End(_, x[N] / 2))
else
Text.Start(_, (x[N] - 1) / 2) = Text.Reverse(Text.End(_, (x[N] - 1) / 2))
),
d = List.Transform(c, Number.From),
e = List.Count(d),
f = List.Sum(d)
in
[Count = e, Sum = f]
)[[A]],
Sol = Table.ExpandRecordColumn(Pros, "A", Record.FieldNames(Pros[A]{0}))
in
SolPower Query solution 2 for Palindrome Count and Sum by Digit, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
N = x,
A = {Number.Power(10, x - 1) .. Number.Power(10, x) - 1},
B = List.Transform(A, Text.From),
C = List.Select(B, each _ = Text.Reverse(_)),
D = List.Count(C),
E = List.Transform(C, Number.From),
F = List.Sum(E),
G = {D} & {F}
in
G,
H = Table.AddColumn(S, "Count", each Fx([N]){0}),
Sol = Table.AddColumn(H, "Sum", each Fx([N]){1})
in
SolPower Query solution 3 for Palindrome Count and Sum by Digit, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.ExpandTableColumn(
Table.AddColumn(
Source,
"r",
each
let
z = Number.IntegerDivide([N], 2),
a = List.Transform(
List.Numbers(Number.Power(10, z - 1), Number.Power(10, z) - Number.Power(10, z - 1)),
Text.From
),
b = List.Transform(a, Text.Reverse),
c = List.Combine(
List.Transform(
a,
each List.Transform({"0" .. "9"}, (y) => Number.From(_ & y & Text.Reverse(_)))
)
),
d =
if Number.IsEven([N]) then
List.Transform(List.Zip({a, b}), each Number.From(_{0} & _{1}))
else
c
in
Table.FromColumns({{List.Count(d)}, {List.Sum(d)}}, {"Count", "Sum"})
),
"r",
{"Count", "Sum"}
)
in
solSolving the challenge of Palindrome Count and Sum by Digit with Excel
Excel solution 1 for Palindrome Count and Sum by Digit, proposed by Bo Rydobon 🇹🇭:
=LET(n,
A3:A9-1,
9*10^INT(
n/2
)*(5.5*10^n)^{0,
1})Excel solution 2 for Palindrome Count and Sum by Digit, proposed by Rick Rothstein:
=LET(
r,
A2:A8-2,
n,
9*10^ROUND(
r/2,
),
HSTACK(
n,
55*n*10^r
)
)Excel solution 3 for Palindrome Count and Sum by Digit, proposed by John V.:
=9/10^(1+INT(
-A3:A9/2
))*(55*(10^(A3:A9-2)))Excel solution 4 for Palindrome Count and Sum by Digit, proposed by Kris Jaganah:
=LET(a,
A3:A9,
b,
SEQUENCE(
9,
,
11,
11
),
HSTACK(ROWS(
b
)*(10^INT((a-1)/2)),
10^CEILING((a-2)*3/2,
1)*SUM(
b
)))Excel solution 5 for Palindrome Count and Sum by Digit, proposed by Julian Poeltl:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A3:A9,
LAMBDA(
NN,
LET(
N,
ROUNDDOWN(
NN/2,
0
),
S,
SEQUENCE(
REPT(
9,
N
)*1-IFERROR(
REPT(
9,
N-1
)*1,
0
),
,
IFERROR(
REPT(
9,
N-1
)+1,
1
)
),
M,
MAP(
S,
LAMBDA(
A,
A&TRANSPOSE(
CONCAT(
MID(
A,
SEQUENCE(
LEN(
A
),
,
LEN(
A
),
-1
),
1
)
)
)
)
),
MM,
IF(
ISEVEN(
NN
),
M,
LEFT(
M,
N
)&SEQUENCE(
,
10,
0
)&RIGHT(
M,
N
)
)*1,
TEXTJOIN(
",",
,
COUNT(
MM
),
SUM(
MM
)
)
)
)
)
),
",",
"|"
)Excel solution 6 for Palindrome Count and Sum by Digit, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_n,
A3:A9,
_cnt,
MAP(
_n,
LAMBDA(
a,
9 * 10 ^ QUOTIENT(
a - 1,
2
)
)
),
_ttl,
_cnt * 55 * 10 ^ (_n - 2),
_r,
HSTACK(
_n,
_cnt,
_ttl
),
_r
)Excel solution 7 for Palindrome Count and Sum by Digit, proposed by LEONARD OCHEA 🇷🇴:
=REDUCE(
B2:C2,
A3:A9,
LAMBDA(
h,
n,
LET(
m,
INT(
n/2
),
p,
10^m/10,
s,
SEQUENCE(
9*p,
,
p
),
t,
ISEVEN(
n
),
r,
IF(
t,
1,
SEQUENCE(
10,
,
0
)
),
d,
DROP(
REDUCE(
"",
r,
LAMBDA(
a,
b,
VSTACK(
a,
MAP(
s,
LAMBDA(
c,
c&IF(
t,
"",
b
)&CONCAT(
MID(
c,
n-SEQUENCE(
n
)+1,
1
)
)
)
)
)
)
),
1
),
VSTACK(
h,
HSTACK(
ROWS(
d
),
SUM(
--d
)
)
)
)
)
)Excel solution 8 for Palindrome Count and Sum by Digit, proposed by 🇵🇪 Ned Navarrete C.:
=LET(n,
A3:A9,
f,
ROUNDUP(
n/2,
),
--({9,
495}&REPT(
0,
HSTACK(
f-1,
n+f-3
)
)))Excel solution 9 for Palindrome Count and Sum by Digit, proposed by El Badlis Mohd Marzudin:
=TEXTSPLIT(TEXTJOIN(";",,MAP(A3:A7, LAMBDA(x, LET(ed,REPT(9,x),st,1&REPT(0,x-1),sq,SEQUENCE(ed-st+1,,st),rv,MAP(sq,LAMBDA(w,TEXTJOIN("",,MID(w,SEQUENCE(LEN(w),,LEN(w),-1),1)))),pl,FILTER(sq,rv=sq&""), COUNT(pl)&"|"&SUM(pl))))),"|",";")Solving the challenge of Palindrome Count and Sum by Digit with Python
Python solution 1 for Palindrome Count and Sum by Digit, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "495 Sum of N Digit Palindrome Numbers.xlsx"
input = pd.read_excel(path, usecols = "A", skiprows = 1)
test = pd.read_excel(path, usecols = "B:C", skiprows = 1)
def generate_all_palindromes(num_digits):
if num_digits < 1:
raise ValueError("Number of digits must be at least 1")
if num_digits == 1:
return list(range(10))
half_digits = (num_digits + 1) // 2
start_num = 10 ** (half_digits - 1)
end_num = 10 ** half_digits - 1
palindromes = []
for i in range(start_num, end_num + 1):
num_str = str(i)
rev_str = num_str[::-1]
if num_digits % 2 == 0:
palindrome_str = num_str + rev_str
else:
palindrome_str = num_str + rev_str[1:]
palindromes.append(int(palindrome_str))
return palindromes
result = input.assign(palindromes = input['N'].map(generate_all_palindromes))
.assign(Count = lambda x: x['palindromes'].map(len),
Sum = lambda x: x['palindromes'].map(sum))
.loc[:, ['Count', 'Sum']]
print(result.equals(test)) # True
Solving the challenge of Palindrome Count and Sum by Digit with Python in Excel
Python in Excel solution 1 for Palindrome Count and Sum by Digit, proposed by Abdallah Ally:
def pallindrome_info(length):
min_num = 10 ** (length - 1)
max_num = 10 ** length
rng = range(min_num, max_num)
numbers = list(filter(lambda x: x == int(str(x)[::-1]), rng))
return len(numbers), sum(numbers)
df = xl("A2:A9", headers=True)
# Perform data wrangling
df[['Count', 'Sum']] = df['N'].map(pallindrome_info).tolist()
df
Solving the challenge of Palindrome Count and Sum by Digit with R
R solution 1 for Palindrome Count and Sum by Digit, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/495 Sum of N Digit Palindrome Numbers.xlsx"
input = read_xlsx(path, range = "A2:A9")
test = read_xlsx(path, range = "B2:C9")
generate_all_palindromes <- function(num_digits) {
if (num_digits < 1) {
stop("Number of digits must be at least 1")
}
if (num_digits == 1) {
return(0:9)
}
half_digits <- ceiling(num_digits / 2)
start_num <- 10^(half_digits - 1)
end_num <- 10^half_digits - 1
palindromes <- vector("integer", length = 0)
for (i in start_num:end_num) {
num_str <- as.character(i)
rev_str <- paste0(rev(strsplit(num_str, "")[[1]]), collapse = "")
if (num_digits %% 2 == 0) {
palindrome_str <- paste0(num_str, rev_str)
} else {
palindrome_str <- paste0(num_str, substring(rev_str, 2))
}
palindromes <- c(palindromes, as.integer(palindrome_str))
}
return(palindromes)
}
result = input %>%
mutate(palindromes = map(N, generate_all_palindromes)) %>%
mutate(Count = map_dbl(palindromes, length),
Sum = map_dbl(palindromes, sum)) %>%
select(Count, Sum)
identical(result, test)
# [1] TRUE
&&&