Let’s say i and j are positions and e(i) and e(j) are elements from the string at that position. Count all inversions. An inversion exists if i < j and e(i) > e(j). Ex. 534 – Possible pairs are (5,3), (5,4), (3,4) where i < j. Now pairs meeting inversion criterion of e(i) > e(j) are (5,3) and (5,4) – Hence, inversion count is 2. Ex. 4321 – Possible pairs are (4,3), (4,2), (4,1), (3,2), (3,1), (2,1) where i < j – All meet inversion criterion of e(i) > e(j). Hence, inversion count is 6.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 396
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Inversions in Number Sequence with Power Query
Power Query solution 1 for Count Inversions in Number Sequence, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
R = Table.AddColumn(S, "R", each
let
a = Text.ToList([String]), b = {0..List.Count(a) - 1},
c = List.TransformMany(b, each b, (r, c) => Number.From(r < c and a{r} > a{c}))
in
List.Sum(c)
)[[R]]
in
R
Blessings!
Power Query solution 2 for Count Inversions in Number Sequence, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList([String]),
b = List.Transform(a, Number.From),
c = (x, y) =>
let
t = List.Transform({1 .. List.Count(x) - 1}, each {x{0}, x{_}})
in
if List.Count(x) = 1 then y else @c(List.Skip(x), y & t),
d = c(b, {}),
e = List.Select(d, each _{0} > _{1})
in
List.Count(e)
)
in
SolPower Query solution 3 for Count Inversions in Number Sequence, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
a = Text.ToList(Text.From(x)),
b = List.Count(a),
c = List.Transform(
{0 .. Number.Power(2, b) - 1},
(i) =>
List.Transform(
{0 .. b - 1},
(j) =>
if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1 then a{j} else null
)
),
d = List.Skip(List.Transform(c, each List.RemoveNulls(_))),
e = List.Select(d, each List.Count(_) = 2),
f = List.Select(e, each _{0} > _{1}),
g = List.Count(f)
in
g,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([String]))[[Answer Expected]]
in
SolPower Query solution 4 for Count Inversions in Number Sequence, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.Count(
List.Combine(
List.Transform(
{0 .. Text.Length([String]) - 2},
(x) =>
List.Select(
List.Transform(
List.RemoveFirstN(
Text.ToList(Text.Range([String], x, Text.Length([String]) - x)),
1
),
(y) => {Text.Middle([String], x, 1), y}
),
(z) => z{0} > z{1}
)
)
)
)
)
in
#"Added Custom"Solving the challenge of Count Inversions in Number Sequence with Excel
Excel solution 1 for Count Inversions in Number Sequence, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
LEN(
a
)
),
m,
MID(
a,
s,
1
),
SUM((mTOROW(
s
)))))) Excel solution 2 for Count Inversions in Number Sequence, proposed by John V.:
=MAP(A2:A10,
LAMBDA(x,
LET(s,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
s,
1
),
SUM((sTOROW(
b
)))))) Excel solution 3 for Count Inversions in Number Sequence, proposed by محمد حلمي:
=MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
LEN(
a
)
),
i,
MID(
a,
s,
1
),
SUM((s>TOROW(
s
))*(iExcel solution 4 for Count Inversions in Number Sequence, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
d,
MID(
a,
s,
1
),
SUM(
MAP(
d,
s,
LAMBDA(
a,
b,
SUM(
N(
a>DROP(
d,
b-1
)
)
)
)
)
)
)
)
)Excel solution 5 for Count Inversions in Number Sequence, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
b,
REDUCE(
"",
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
x,
y,
VSTACK(
x,
x&y
)
)
),
c,
FILTER(
b,
LEN(
b
)=2
),
COUNT(
IF(
RIGHT(
c
)Excel solution 6 for Count Inversions in Number Sequence, proposed by Julian Poeltl:
=BYROW(A2:A10,
LAMBDA(StringCol,
LET(String,
StringCol,
Len,
Len(
String
),
SEQ,
SEQUENCE(
Len
),
Part,
MID(
String,
SEQ,
1
),
ARR,
SUM((PartTRANSPOSE(
SEQ
))),
ARR)))
2. approach:
=MAP(A2:A10,
LAMBDA(StrCol,
LET(String,
StrCol,
Len,
LEN(
String
),
SEQ,
SEQUENCE(
Len
),
Part,
MID(
String,
SEQ,
1
),
ARR,
Part1,
1,
0),
SUM(
LowTRIMA*ARR
)))) Excel solution 7 for Count Inversions in Number Sequence, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
SUM(
MAKEARRAY(
LEN(
z
),
LEN(
z
),
LAMBDA(
x,
y,
--IF(
x--MID(
z,
y,
1
)
)
)
)
)
)
) Excel solution 8 for Count Inversions in Number Sequence, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
_s,
SEQUENCE(
LEN(
a
)
),
_r,
TOROW(
_s
),
SUM(
IFERROR(
TOCOL(
IF(
IF(
_s < _r,
MID(
a,
_s,
1
) > MID(
a,
_r,
1
)
),
1,
x
),
3
),
0
)
)
)
)
)Excel solution 9 for Count Inversions in Number Sequence, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,
LAMBDA(x,
SUM(MAP(SEQUENCE(
LEN(
x
)-1
),
LAMBDA(a,
SUM(--(MID(
x,
a,
1
)>MID(
x,
SEQUENCE(
LEN(
x
)-a,
,
a+1
),
1
))))))))Excel solution 10 for Count Inversions in Number Sequence, proposed by Tyler Cameron:
=MAP(A2:A10,
LAMBDA(u,
LET(a,
LEN(
u
),
b,
MID(
u,
SEQUENCE(
a
),
1
),
SUM(--IFERROR(MAKEARRAY(a,
a,
LAMBDA(r,
c,
INDEX(b,
r+(c-1))Solving the challenge of Count Inversions in Number Sequence with Python
Python solution 1 for Count Inversions in Number Sequence, proposed by Giorgi Goderdzishvili:
df = pd.read_clipboard()
lst = [int(i.replace(',','')) for i in df.String]
fin = []
for el in lst:
cnting = 0
for i in range(len(str(el))):
for j in range(i+1,len(str(el))):
if str(el)[i]>str(el)[j]:
cnting+=1
fin.append(cnting)
for i in fin:
print(i)
Solving the challenge of Count Inversions in Number Sequence with Python in Excel
Python in Excel solution 1 for Count Inversions in Number Sequence, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_396 - Count Inversions.xlsx'
df = pd.read_excel(file_path)
def count_inversion(col):
inversions = []
for i in range(len(str(col)) - 1):
for char in str(col)[i + 1:]:
if str(col)[i] > char:
inversions.append((str(col)[i], char))
return len(inversions)
df['My Answer'] = df['String'].apply(count_inversion)
print(df)
https://github.com/mathematiciantz/Excel_BI_Challenges/blob/main/Excel_Challenge_396_Count_Inversions.py
Solving the challenge of Count Inversions in Number Sequence with R
R solution 1 for Count Inversions in Number Sequence, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/396 Count Inversions.xlsx", range = "A1:A10")
test = read_excel("Excel/396 Count Inversions.xlsx", range = "B1:B10")
check_inversions = function(x) {
x = as.character(x)
inversions = 0
for (i in 1:(nchar(x) - 1)) {
for (j in (i + 1):nchar(x)) {
if (as.numeric(substr(x, i, i)) > as.numeric(substr(x, j, j))) {
inversions = inversions + 1
}
}
}
return(inversions)
}
result = input %>%
mutate(inversions = map_dbl(String, check_inversions))
Solving the challenge of Count Inversions in Number Sequence with Excel VBA
Excel VBA solution 1 for Count Inversions in Number Sequence, proposed by Hiran de Silva FCMA:
Sub ExcelBI_396()
iRow = 2
strSource = Cells(iRow, 1).Value
iStart = 1
jStart = 1
Do Until Cells(iRow, 1).Value = ""
intInversionCount = 0
strSource = Cells(iRow, 1).Value
For i = 1 To Len(strSource)
ei = Mid(strSource, i, 1)
For j = 1 To Len(strSource)
ej = Mid(strSource, j, 1)
If i < j And ei > ej Then
intInversionCount = intInversionCount + 1
End If
Next j
Next i
Cells(iRow, 3).Value = intInversionCount
iRow = iRow + 1
Loop
End Sub
&&&