Find whether a sequence is increasing (I), decreasing (D) or neither of these (N). A sequence is increasing if every succeeding term > previous term A sequence is decreasing if every succeeding term < previous term
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 655
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Detect Sequence Direction with Power Query
Power Query solution 1 for Detect Sequence Direction, proposed by Kris Jaganah:
"Answer Expected", each [
a = List.Transform( Text.Split([Sequences],", ") ,(v)=> Number.From(v)) ,
b = List.Max(a) ,
c = List.Min(a) ,
d = List.Last(a),
e = if b = c then "N" else if a{0} = c and d = b then "I" else
if a{0} = b and d = c then "D" else "N"][e])
Power Query solution 2 for Detect Sequence Direction, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(
Tabela1,
"Resposta",
each
let
a = Text.Split([Sequences], ", "),
b = List.Zip({a, List.Skip(a)}),
c = List.RemoveLastN(
List.Transform(
{0 .. List.Count(b) - 1},
(x) => Value.Compare(Number.From(b{x}{0}), Number.From(b{x}{1}))
)
),
d = Text.Combine(List.Transform(List.Distinct(c), Text.From)),
e = if d = "1" then "D" else if d = "-1" then "I" else "N"
in
e
)
in
FontePower Query solution 3 for Detect Sequence Direction, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (lst1, lst2) => List.AllTrue(List.Transform(List.Zip({lst1, lst2}), (x) => x{0} > x{1})),
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = List.Transform(Text.Split([Sequences], ", "), Number.From),
b = {List.Skip(a), List.RemoveLastN(a, 1)},
c = if f(b{0}, b{1}) then "I" else if f(b{1}, b{0}) then "D" else "N"
][c]
),
Result = Table.AddColumn(AddCol, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 4 for Detect Sequence Direction, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Transform(S[Sequences], each Text.Split(_, ", ")),
Fx = (x) =>
let
b = List.Transform(x, Number.From),
c = List.Generate(
() => [i = 0],
each [i] < List.Count(b) - 1,
each [i = [i] + 1],
each if b{[i]} < b{[i] + 1} then "I" else if b{[i]} > b{[i] + 1} then "D" else "N"
),
d =
if List.ContainsAll({"I"}, c) then
"I"
else if List.ContainsAll({"D"}, c) then
"D"
else
"N"
in
d,
Sol = Table.FromColumns({List.Transform(a, each Fx(_))}, {"Answer Expected"})
in
SolPower Query solution 5 for Detect Sequence Direction, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.AddColumn(Source, "Answer Expected", Fun),
Fun = each [
A = List.TransformMany(Record.ToList(_), (x) => Text.Split(x, ","), (x, y) => Number.From(y)),
B = List.Sort(A, 0),
C = List.Sort(A, 1),
D = if B = C or (A <> B and A <> C) then "N" else if A = B then "I" else "D"
][D]
in
ResPower Query solution 6 for Detect Sequence Direction, proposed by Rafael González B.:
let
Source = Question_Table,
Result = Table.TransformColumns(Source, {"Sequences", each
let
_sP = Text.Split(_, ", "),
_lT = List.Transform(_sP, (x) => Number.From(x)),
_lA = List.Accumulate(
{1..List.Count(_sP) - 1},
{},
(An, Ac) => [
a = Ac = 1,
b = _lT{Ac} - _lT{Ac -1},
c = _lT{1} - _lT{0},
d = if a then c else b,
e = if d > 0 then "I" else if d < 0 then "D" else "N",
f = An & {e}
][f]),
_lD = List.Distinct(_lA),
_Ch = if List.Count(_lD) = 1 then _lD{0} else "N"
in
_Ch})
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 7 for Detect Sequence Direction, proposed by Peter Krkos:
PowerQuery solution:
= Table.AddColumn(Source, "Answer", each
[ a = List.Transform(Text.Split([Sequences], ", "), Number.FromText),
b = List.Zip({ List.RemoveLastN(a), List.Skip(a) }),
c = List.Distinct(List.Accumulate(b, {}, (st, cur)=> st & { Value.Compare(cur{0}, cur{1}) } )),
d = if List.Count(c) > 1 then "N" else List.ReplaceMatchingItems({c{0}}, {{-1, "I"}, {1, "D"}, {0, "N"}}){0}
][d], type text)
Power Query solution 8 for Detect Sequence Direction, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer",
each [
a = List.Transform(Text.Split([Sequences], ","), Number.From),
b = List.Count(a) - 1,
c = List.Generate(() => 0, each _ < b, each _ + 1, each Value.Compare(a{_}, a{_ + 1})),
d = {"I", "D", "N"}{List.PositionOf({List.Sum(c) = - b, List.Sum(c) = b, true}, true)}
][d]
)
in
BPower Query solution 9 for Detect Sequence Direction, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
f = (seq as list) as text => let
comp = List.Transform(List.Zip({List.RemoveLastN(seq, 1), List.Skip(seq)}), each Value.Compare(_{0}, _{1})),
n = List.Count(comp),
s = List.Sum(comp),
a = if s = -n then "I" else if s = n then "D" else "N"
in a,
Answer = Table.AddColumn(Source, "Result", each f(Expression.Evaluate("{"&[Sequences]&"}")))
in
Answer
I've missed taking part, absolutely ❤ your challenges!
Power Query solution 10 for Detect Sequence Direction, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Sequences", type text}, {"Answer Expected", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
t = Text.Split([Sequences], ","),
l = List.Transform(
{1 .. List.Count(t) - 1},
each
let
p = Number.From(t{_}),
s = Number.From(t{_ - 1})
in
if p > s then "I" else if p < s then "D" else "N"
),
r =
if List.IsEmpty(List.RemoveItems(l, {"I"})) then
"I"
else if List.IsEmpty(List.RemoveItems(l, {"D"})) then
"D"
else
"N"
][r]
)
in
#"Added Custom"Power Query solution 11 for Detect Sequence Direction, proposed by Oleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "SourceTable"]}[Content],
from = List.Transform(src[Sequences], (x) => List.Transform(Text.Split(x, ","), Number.From)),
dif = List.Transform(
from,
(lst) =>
[
zip = List.Zip({List.RemoveLastN(lst, 1), List.Skip(lst)}),
tr = List.Transform(zip, (x) => List.Sum({x{1}, - x{0}}))
][tr]
),
f = (x) =>
[
nums = List.Buffer(x),
dyn =
if List.MatchesAll(nums, each _ > 0) then
"I"
else if List.MatchesAll(nums, each _ < 0) then
"D"
else
"N"
][dyn],
res = List.Transform(dif, (x) => f(x))
in
resSolving the challenge of Detect Sequence Direction with Excel
Excel solution 1 for Detect Sequence Direction, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
N,
TEXTSPLIT(
a,
,
", "
),
s,
DROP(
N,
1
)-DROP(
N,
-1
),
IFS(
AND(
s>0
),
"I",
AND(
s<0
),
"D",
1,
"N"
)
)
)
)Excel solution 2 for Detect Sequence Direction, proposed by Rick Rothstein:
=MAP(A2:A8,LAMBDA(x,LET(n,0+TEXTSPLIT(x,,","),x,DROP(n,-1),y,DROP(n,1),IF(AND(x>y),"D",IF(AND(xExcel solution 3 for Detect Sequence Direction, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
s,
TEXTSPLIT(
x,
,
","
),
i,
DROP(
s,
1
)-DROP(
s,
-1
),
IFS(
AND(
i<0
),
"I",
AND(
i>0
),
"D",
1,
"N"
)
)
)
)Excel solution 4 for Detect Sequence Direction, proposed by Kris Jaganah:
=MAP(A2:A8,
LAMBDA(x,
LET(a,
--TEXTSPLIT(
x,
,
", "
),
b,
MIN(
a
),
c,
MAX(
a
),
d,
TAKE(
a,
-1
),
IFS(b=c,
"N",
(b=@a)*(c=d),
"I",
(c=@a)*(b=d),
"D",
1,
"N"))))Excel solution 5 for Detect Sequence Direction, proposed by Julian Poeltl:
=MAP(A2:A8,LAMBDA(D,LET(S,--TEXTSPLIT(D,,","),IF(PRODUCT(--DROP(DROP(S,1)>S,-1)),"I",IF(PRODUCT(--DROP(DROP(S,1)Excel solution 6 for Detect Sequence Direction, proposed by Timothée BLIOT:
=MAP(A2:A8,LAMBDA(z,LET(A,--REGEXEXTRACT(z,"-?d+",1), SWITCH(1,PRODUCT(--DROP(A>DROP(A,,1),,-1)),"D",PRODUCT(--DROP(AExcel solution 7 for Detect Sequence Direction, proposed by Hussein SATOUR:
=MAP(A2:A8,LAMBDA(x,LET(a,--TEXTSPLIT(x,,", "),b,DROP(a,1),c,DROP(a,-1),d,UNIQUE(b>c),IFS(OR(COUNTA(d)=2,UNIQUE(b-c)=0),"N",d,"I",1,"D"))))Excel solution 8 for Detect Sequence Direction, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A8,
LAMBDA(a,
LET(t,
TEXTSPLIT(
a,
,
", "
),
i,
CORREL(
XMATCH(
t,
t
),
-t
),
IFERROR(IF((i>0)/(ABS(
i
)>0.9),
"D",
"I"),
"N"))))Excel solution 9 for Detect Sequence Direction, proposed by Duy Tùng:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
f,
LAMBDA(
v,
ARRAYTOTEXT(
SORT(
a,
,
v
)
)
),
IF(
ROWS(
UNIQUE(
a
)
)=1,
"N",
IF(
x=f(
-1
),
"D",
IF(
x=f(
1
),
"I",
"N"
)
)
)
)
)
)Excel solution 10 for Detect Sequence Direction, proposed by Sunny Baggu:
=MAP(
A2:A8,
LAMBDA(t,
LET(
_ts, TEXTSPLIT(t, , ", ") + 0,
_a, DROP(_ts, 1),
_b, DROP(_ts, -1),
IFS(AND(_a > _b), "I", AND(_a < _b), "D", 1, "N")
)
)
)Excel solution 11 for Detect Sequence Direction, proposed by Abdallah Ally:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
b,
DROP(
a,
1
)> DROP(
a,
-1
),
IFS(
AND(
b
),
"I",
NOT(
OR(
b
)
),
"D",
1,
"N"
)
)
)
)Excel solution 12 for Detect Sequence Direction, proposed by Anshu Bantra:
=MAP(
A2:A8,
LAMBDA(ro_,
LET(
lst_, --(TEXTSPLIT(ro_, , ", ")),
org_, TEXTJOIN("", , lst_),
asc_, TEXTJOIN("", , SORT(lst_, , 1)),
dec_, TEXTJOIN("", , SORT(lst_, , -1)),
IFS(asc_ = dec_, "N", org_ = dec_, "D", org_ = asc_, "I", TRUE, "N")
)
)
)Excel solution 13 for Detect Sequence Direction, proposed by Anshu Bantra:
= [int(_) for _ in lst.split(", ")]
increasing = all(lst[i] < lst[i + 1] for i in range(len(lst) - 1))
decreasing = all(lst[i] > lst[i + 1] for i in range(len(lst) - 1))
if increasing:
return 'I'
elif decreasing:
return 'D'
else:
return 'N'
df = to_df(REF("A1:A8"))
df['Answer'] = df['Sequences'].apply(lst_order)Excel solution 14 for Detect Sequence Direction, proposed by Md. Zohurul Islam:
=IFERROR(
MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
UNIQUE(
TEXTSPLIT(
x,
,
", "
)
),
b,
DROP(
& a,
-1
)-DROP(
a,
1
),
d,
ABS(
b<0
),
e,
UNIQUE(
d
),
f,
COUNT(
e
),
IFS(
AND(
f=1,
e>0
),
"I",
AND(
f=1,
e=0
),
"D",
TRUE,
"N"
)
)
)
),
"N"
)Excel solution 15 for Detect Sequence Direction, proposed by Asheesh Pahwa:
=MAP(A2:A8,LAMBDA(x,LET(a,--TEXTSPLIT(x,","),b,DROP(a,,-1)-DROP(a,,1),IFS(AND(b<0),"I",AND(b>0),"D",1,"N"))))Excel solution 16 for Detect Sequence Direction, proposed by ferhat CK:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
","
),
b,
DROP(
a,
,
-1
)-DROP(
a,
,
1
),
r,
COUNT(
b
),
IFS(
SUM(
N(
b<0
)
)=r,
"I",
SUM(
N(
b>0
)
)=r,
"D",
1,
"N"
)
)
)
)Excel solution 17 for Detect Sequence Direction, proposed by Jaroslaw Kujawa:
=BYROW(A2:A8;
LAMBDA(a;
LET(b;
TEXTSPLIT(
a;
", "
);
c;
LEN(
a
)-LEN(
SUBSTITUTE(
a;
",";
""
)
);
d;
TAKE(
b;
;
c
)-DROP(
b;
;
1
);
IF(SUM(1*(d<0))=c;
"I";
IF(SUM(1*(d>0))=c;
"D";
"N")))))Excel solution 18 for Detect Sequence Direction, proposed by Ankur Sharma:
=MAP(A2:A8, LAMBDA(a,
LET(o, --TEXTSPLIT(a, ", "),
f, DROP(o, , -1),
s, DROP(o, , 1),
IF(SUM(--(s > f)) = COUNT(f), "I", IF(SUM(--(s < f)) = COUNT(f), "D", "N")))))Excel solution 19 for Detect Sequence Direction, proposed by JvdV -:
=MAP(A1:A3,LAMBDA(x,LET(s,--TEXTSPLIT(x,,","),f,SORT,a,AND,IFS(a(f(s)=s),"I",a(f(s,,-1)=s),"D",1,"N"))))Excel solution 20 for Detect Sequence Direction, proposed by Erdit Qendro:
=DROP(REDUCE("",A2:A8,
LAMBDA(accum,rwT,
LET(rwTtoRws,TOROW(N(--TEXTSPLIT(rwT,",")),3),
x,SORT(rwTtoRws,,1,1),y,SORT(rwTtoRws,,-1,1),
l,LAMBDA(l,CONCAT(l)),result,
IF(l(x)=l(y),"N",IF(l(x)=l(rwTtoRws),"I",IF(l(y)=l(rwTtoRws),"D","N"))),
VSTACK(accum,result)))),1)Excel solution 21 for Detect Sequence Direction, proposed by Jorge Alvarez:
=MAP(
A2:A8;
LAMBDA(
cad;
LET(
val;
--DIVIDIRTEXTO(
cad;
;
", "
);
BYCOL(
val;
LAMBDA(
v;
LET(
_r1;
EXCLUIR(
v;
1
);
_r2;
EXCLUIR(
v;
-1
);
SI.CONJUNTO(
Y(
_r1>_r2
);
"I";
Y(
_r1<_r2
);
"D";
VERDADERO;
"N"
)
)
)
)
)
)
)Excel solution 22 for Detect Sequence Direction, proposed by CA Mohit Saxena:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
", "
),
c,
DROP(
b,
,
1
)-DROP(
b,
,
-1
),
IFS(
AND(
c›0
),
"I",
AND(
c‹0
),
"D",
1,
"N"
)
)
)
)Excel solution 23 for Detect Sequence Direction, proposed by Casper Badenhorst:
=CORREL(
array1,
array2
)Solving the challenge of Detect Sequence Direction with Python
Python solution 1 for Detect Sequence Direction, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "655 Increasing or Decreasing or None Sequences.xlsx"
input = pd.read_excel(path, usecols="A", nrows=8)
test = pd.read_excel(path, usecols="B", nrows=8)
input['rn'] = input.index + 1
input = input.assign(Sequences=input['Sequences'].str.split(',')).explode('Sequences').astype({'Sequences': int})
input['diff'] = input.groupby('rn')['Sequences'].diff()
result = input.dropna().groupby('rn').apply(
lambda x: pd.Series({'Answer Expected': 'I' if all(x['diff'] > 0) else 'D' if all(x['diff'] < 0) else 'N'})
).reset_index()
result = result.drop(columns='rn')
print(result.equals(test))
Python solution 2 for Detect Sequence Direction, proposed by Luan Rodrigues:
import pandas as pd
file = "Excel_Challenge_655 - Increasing or Decreasing or None Sequences.xlsx"
df = pd.read_excel(file,usecols='A')
def transform(x):
a = x.split(', ')
b = list(zip(a,a[1:]))
c = [int(b[i][0]) - int(b[i][1]) for i in range(len(b)) ]
d = ','.join(set(['D' if i > 0 else 'I' if i < 0 else 'N' for i in c]))
return 'N' if d == 'I,D' else 'N' if d == 'D,I' else d
df['Result'] = df['Sequences'].apply(transform)
print(df)
Python solution 3 for Detect Sequence Direction, proposed by Abdallah Ally:
import pandas as pd
# Create functions to categorize a sequence
def all_true(lst1, lst2):
return all(x[0] > x[1] for x in zip(lst1, lst2))
def categorise_sequence(str_seq):
split = list(map(int, str_seq.split(', ')))
if all_true(split[1:], split[:-1]):
return 'I'
elif all_true(split[:-1], split[1:]):
return 'D'
return 'N'
file_path = 'Excel_Challenge_655 - Increasing or Decreasing or None Sequences.xlsx'
df = pd.read_excel(io=file_path)
# Perform data manipulation
df['My Answer'] = df['Sequences'].map(categorise_sequence)
df['Check'] = df['My Answer'] == df['Answer Expected']
df
Solving the challenge of Detect Sequence Direction with Python in Excel
Python in Excel solution 1 for Detect Sequence Direction, proposed by Alejandro Campos:
s = list(map(int, seq.split(",")))
return 'I' if all(x < y for x, y in zip(s, s[1:])) else 'D' if all(x > y for x, y in zip(s, s[1:])) else 'N'
sequences = xl("A1:A8", headers=True)
sequences
Python in Excel solution 2 for Detect Sequence Direction, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:A8", True)
df["Answer"] = df["Sequences"].map(lambda x: [int(i.strip()) for i in x.split(",")])
def MyFun(seq):
diff = np.diff(seq)
return "I" if np.all(diff > 0) else "D" if np.all(diff < 0) else "N"
df["Answer"] = df["Answer"].map(MyFun)
df
Solving the challenge of Detect Sequence Direction with R
R solution 1 for Detect Sequence Direction, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/655 Increasing or Decreasing or None Sequences.xlsx"
input = read_excel(path, range = "A1:A8")
test = read_excel(path, range = "B1:B8")
result = input %>%
mutate(rn = row_number()) %>%
separate_rows(Sequences, sep = ",", convert = TRUE) %>%
mutate(diff = Sequences - lag(Sequences), .by = rn) %>%
na.omit() %>%
summarise(`Answer Expected` = case_when(all(diff > 0) ~ "I",
all(diff < 0) ~ "D",
TRUE ~ "N"), .by = rn) %>%
select(-rn)
all.equal(result, test)
#> [1] TRUE
&&