Wavy numbers – List the numbers where digits alternately get smaller or larger (they should not be equal). Ex. 132 => 1 < 3 > 2 9031 => 9 > 0 < 3 > 1
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 474
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Detect Wavy Number Pattern with Power Query
Power Query solution 1 for Detect Wavy Number Pattern, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each
let
n = Text.ToList(Text.From([Numbers]))
in
List.AllTrue(
List.Transform(
{1 .. List.Count(n) - 2},
(i) =>
Expression.Evaluate(
"(" & n{i} & "-" & n{i - 1} & ")*(" & n{i} & "-" & n{i + 1} & ")>0"
)
)
)
)
in
AnsPower Query solution 2 for Detect Wavy Number Pattern, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each
let
n = List.Transform(Text.ToList(Text.From([Numbers])), Number.From)
in
List.AllTrue(
List.Transform({1 .. List.Count(n) - 2}, (i) => (n{i} - n{i - 1}) * (n{i} - n{i + 1}) > 0)
)
)
in
AnsPower Query solution 3 for Detect Wavy Number Pattern, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
T = Text.From([Numbers]),
L = Text.ToList(T),
G = List.Accumulate(
List.Skip(L, 2),
[a = L{0}, b = L{1}, c = if b > a then ">" else "<", e = {true}],
(x, y) => [
a = x[b],
b = y,
c = if x[c] = "<" then ">" else "<",
d = if c = "<" then b < a else b > a,
e = x[e] & {d and a <> b}
]
)[e],
R = List.AllTrue(G)
][R]
)
in
ReturnPower Query solution 4 for Detect Wavy Number Pattern, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
(x) =>
let
a = Text.ToList(Text.From(x[Numbers])),
b = g(a, Number.From),
c = g({1 .. List.Count(b) - 1}, each b{_} - b{_ - 1}),
d = List.Alternate(c, 1, 1),
e = List.Alternate(c, 1, 1, 1),
f = List.AllTrue,
g = List.Transform,
h =
if f(g(d, each _ > 0))
and f(g(e, each _ < 0)) or f(g(d, each _ < 0))
and f(g(e, each _ > 0))
then
true
else
false
in
h
)
in
SolPower Query solution 5 for Detect Wavy Number Pattern, proposed by Brian Julius:
let
Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "rng"]}[Content]),
AddIsWavy = Table.AddColumn(
Source,
"IsWavy",
each [
a = [Numbers],
b = Text.ToList(Text.From(a)),
c = List.Transform(b, each Number.From(_)),
d = List.Positions(c),
e = List.Transform(
d,
each try if c{_} > c{_ + 1} then "-" else if c{_} < c{_ + 1} then "+" else "0" otherwise null
),
f = Text.Combine(e, ""),
g = if Text.Contains(f, "0") or Text.Contains(f, "--") or Text.Contains(f, "++") then 0 else 1
][g]
),
Filter = Table.RemoveColumns(Table.SelectRows(AddIsWavy, each [IsWavy] = 1), "IsWavy")
in
FilterPower Query solution 6 for Detect Wavy Number Pattern, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (x) =>
let
a = Text.ToList(Text.From(x)),
b = {1 .. List.Count(a) - 2},
c = List.Accumulate(
b,
true,
(s, t) =>
s and ((a{t} > a{t - 1} and a{t} > a{t + 1}) or (a{t} < a{t - 1} and a{t} < a{t + 1}))
)
in
c,
Results = List.Select(Source[Numbers], each f(_))
in
ResultsPower Query solution 7 for Detect Wavy Number Pattern, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList(Text.From([Numbers])),
b = List.Accumulate(
{1 .. List.Count(a) - 2},
true,
(state, current) =>
state
and (
(a{current - 1} < a{current} and a{current} > a{current + 1})
or (a{current - 1} > a{current} and a{current} < a{current + 1})
)
)
in
b
),
SelectRows = Table.SelectRows(AddCol, each [Answer] = true)[Numbers]
in
SelectRowsSolving the challenge of Detect Wavy Number Pattern with Excel
Excel solution 1 for Detect Wavy Number Pattern, proposed by Bo Rydobon 🇹🇭:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
a/AND(
MAP(
SEQUENCE(
LEN(
a
)-2
),
LAMBDA(
s,
PRODUCT(
MID(
a,
s+1,
1
)-MID(
a,
s+{0,
2},
1
)
)
)
)>0
)
)
),
3
)Excel solution 2 for Detect Wavy Number Pattern, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A10,
s,
SEQUENCE(
9
),
p,
"=[0-"&s-1&"])|b)",
FILTER(
a,
1-REGEXTEST(
REGEXREPLACE(
a,
TEXTJOIN(
"|",
,
"((?<"&p&s&"((?"&p
),
"-"
),
"dd"
)
)
)
Pattern
=LET(
s,
SEQUENCE(
9
),
p,
"=[0-"&s-1&"])|b)",
TEXTJOIN(
"|",
,
"((?<"&p&s&"((?"&p
)
)Excel solution 3 for Detect Wavy Number Pattern, proposed by John V.:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(n,
LEN(
x
),
s,
SEQUENCE(
n-1
),
x/(ABS(
SUM(
SIGN(
MID(
x,
s,
1
)-MID(
x,
1+s,
1
)
)
)
)=ABS(
ISEVEN(
n
)
))))),
2)Excel solution 4 for Detect Wavy Number Pattern, proposed by محمد حلمي:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
n,
LET(
s,
SEQUENCE(
LEN(
n
)
),
d,
MID(
n,
s,
1
),
n/AND(
MAP(
s,
d,
LAMBDA(
a,
i,
LET(
v,
CHOOSEROWS(
d,
a+{-1,
1}
),
IFERROR(
OR(
AND(
i>v
),
AND(
iExcel solution 5 for Detect Wavy Number Pattern, proposed by محمد حلمي:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
x/LET(
s,
SEQUENCE(
LEN(
x
)-2
),
i,
MID(
x,
s+1,
1
),
SUM(
SIGN(
i-MID(
x,
s,
1
)
)-SIGN(
i-MID(
x,
s+2,
1
)
)
)=0
)
)
),
2
)Excel solution 6 for Detect Wavy Number Pattern, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
--REGEXEXTRACT(
x,
"[0-9]",
1
),
b,
N((DROP(
a,
-1
)-DROP(
a,
1
))>0),
c,
IFNA(
WRAPROWS(
b,
2
),
CHOOSEROWS(
b,
2
)
),
d,
MMULT(
c,
{1;1}
),
x/((MAX(
d
)=1)*(MIN(
d
)=1))))),
3)Excel solution 7 for Detect Wavy Number Pattern, proposed by Julian Poeltl:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
A,
LET(
S,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
AND(
DROP(
DROP(
S>DROP(
S,
1
),
-1
)<>DROP(
DROP(
S>DROP(
S,
1
),
-1
),
1
),
-1
)
)
)
)
)
)Excel solution 8 for Detect Wavy Number Pattern, proposed by Timothée BLIOT:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(z,
LET(S,
TAKE,
D,
DROP,
P,
PRODUCT,
A,
LEN(
z
),
B,
--WRAPROWS(
MID(
z,
SEQUENCE(
A
),
1
),
2
),
C,
IF(
ISODD(
A
),
VSTACK(
D(
S(
B,
,
1
),
-1
)-D(
S(
B,
,
-1
),
-1
),
S(
B,
-1,
1
)-D(
S(
B,
-2,
-1
),
-1
)
),
S(
B,
,
1
)-S(
B,
,
-1
)
),
OR(P(--(C<0)),
P(--(C>0)))))))Excel solution 9 for Detect Wavy Number Pattern, proposed by Oscar Mendez Roca Farell:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
LEN(
a
)
),
m,
MID(
a,
s,
1
) ,
d,
DROP(
m,
1
) ,
b,
DROP(
m,
-1
),
a/AND(N(
bExcel solution 10 for Detect Wavy Number Pattern, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
m,
MID(
x,
DROP(
SEQUENCE(
LEN(
x
)
),
-2
) + {0,
1,
2},
1
),
c,
SIGN(
INDEX(
m,
,
2
) - INDEX(
m,
,
1
)
),
d,
SIGN(
INDEX(
m,
,
2
) - INDEX(
m,
,
3
)
),
SUM(
c - d
) = 0
)
)
)
)Excel solution 11 for Detect Wavy Number Pattern, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(x,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
) - 2
) + {0,
1,
2},
1
),
k,
LAMBDA(
g,
INDEX(
m,
,
g
)
),
a,
k(
1
) < k(
2
),
b,
k(
3
) < k(
2
),
c,
k(
1
) > k(
2
),
d,
k(
3
) > k(
2
),
_e1,
LAMBDA(
arr,
BYROW(
arr,
LAMBDA(
a,
AND(
& a
)
)
)
),
e,
_e1(HSTACK(
a,
b
)),
f,
_e1(HSTACK(
c,
d
)),
ROWS(
e
) = SUM(
e + f
)
)
)
)
)Excel solution 12 for Detect Wavy Number Pattern, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(F,
LAMBDA(F,
x,
n,
k,
LET(l,
LEN(
x
),
p,
CONCAT(
k,
N(
MID(
x,
n,
1
)Excel solution 13 for Detect Wavy Number Pattern, proposed by Abdallah Ally:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(x,
LET(a,
x,
b,
LEN(
a
),
c,
MID(
a,
SEQUENCE(
b
),
1
),
d,
SEQUENCE(
b-2,
,
2
),
f,
LAMBDA(
s,
t,
CHOOSEROWS(
s,
t
)
),
REDUCE(1,
d,
LAMBDA(x,
y,
x*(OR(
AND(
f(
c,
y
)>f(
c,
y-1
),
f(
c,
y
)>f(
c,
y+1
)
),
AND(
f(
c,
y
)Excel solution 14 for Detect Wavy Number Pattern, proposed by 🇵🇪 Ned Navarrete C.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
r,
LET(
s,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
r/ISNUMBER(
FIND(
CONCAT(
N(
DROP(
s,
-1
)>DROP(
s,
1
)
)
),
REPT(
10,
9
)
)
)
)
)
),
2
)Excel solution 15 for Detect Wavy Number Pattern, proposed by Pieter de B.:
=TOCOL(A2:A10/MAP(A2:A10,
LAMBDA(z,
LET(m,
LAMBDA(x,
MID(z,
SEQUENCE(,
(LEN(
z
)+1)/2,
x,
2),
1)),
n,
m(
2
),
s,
SIGN(
m(
1
)-IF(
LEN(
n
),
n,
SIGN(
@n
)
)
),
SUM(
s
)=@s*COUNT(
s
)))),
2)Excel solution 16 for Detect Wavy Number Pattern, proposed by Bilal Mahmoud kh.:
=LET(
r,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
RIGHT(
x,
LEN(
x
)-1
),
b,
LEFT(
x,
LEN(
x
)-1
),
c,
--MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
d,
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
e,
MAP(
SEQUENCE(
LEN(
a
)
),
LAMBDA(
n,
IF(
MOD(
n,
2
)=0,
INDEX(
c,
n,
1
)>INDEX(
d,
n,
1
),
INDEX(
c,
n,
1
)INDEX(
d,
n,
1
)
)
)
),
res,
IF(
OR(
SUM(
--e
)=LEN(
a
),
SUM(
--f
)=LEN(
a
)
),
x,
""
),
res
)
)
),
q,
FILTER(
r,
r<>""
),
q
) Excel solution 17 for Detect Wavy Number Pattern, proposed by JvdV -:
=FILTER(
A2:A10,
NOT(
REGEXTEST(
A2:A10,
"((?=0[1-9]|1[2-9]|2[3-9]|3[4-9]|4[5-9]|5[6-9]|6[7-9]|7[89]|89).){2}|(.)2|((?!(?1)).){3}"
)
)
)Excel solution 18 for Detect Wavy Number Pattern, proposed by Sandeep Marwal:
=LET(e,
BYROW(A2:A10,
LAMBDA(p,
LET(
a,
MID(
p,
SEQUENCE(
LEN(
p
)
),
1
),
b,
DROP(
VSTACK(
0,
a
),
-1
),
c,
TEXTJOIN("",
,
ROUNDUP((a-b)/9,
0)),
(c=TEXTBEFORE(
REPT(
"1-1",
ROUNDUP(
LEN(
p
)/2,
0
)+1
),
1,
LEN(
p
)+1
))+(c=TEXTBEFORE(
TEXTBEFORE(
1&REPT(
"1-1",
ROUNDUP(
LEN(
p
)/2,
0
)+1
),
1,
LEN(
p
)+1
),
"-",
-1
))+(c=TEXTBEFORE(
1&REPT(
"1-1",
ROUNDUP(
LEN(
p
)/2,
0
)+1
),
1,
LEN(
p
)+1
))))),
FILTER(
A2:A10,
e
))Excel solution 19 for Detect Wavy Number Pattern, proposed by Burhan Cesur:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(v,
LET(h,
v,
a,
MID(
h,
SEQUENCE(
,
LEN(
h
)
),
1
),
b,
MID(
h,
SEQUENCE(
,
LEN(
h
),
2
),
1
),
c,
a>b,
x,
DROP(
c,
,
-1
),
y,
ISEVEN(
SEQUENCE(
,
LEN(
h
)-1
)
),
BYROW(--(x=IF(
--LEFT(
h,
1
)>--MID(
h,
2,
1
),
NOT(
y
),
y
)),
PRODUCT)))))Excel solution 20 for Detect Wavy Number Pattern, proposed by Erik Oehm:
=LET(
_Input,
A2:A10,
fnIsWavy,
LAMBDA(number,
LET(
_Numbers,
MID(
number,
SEQUENCE(
LEN(
number
)
),
1
),
_Tail,
DROP(
_Numbers,
1
),
_Head,
TRANSPOSE(
DROP(
_Numbers,
-1
)
),
_UpDown,
FILTER(TOCOL((_Tail > _Head) - (_Tail < _Head)),
TOCOL(
MUNIT(
LEN(
number
) - 1
)
)),
_Goal,
2 * MOD(
SEQUENCE(
LEN(
number
) - 1
),
2
) - 1,
_IsWavy,
OR(
AND(
_UpDown = _Goal
),
AND(
_UpDown = -_Goal
)
),
_IsWavy
)),
_Result,
FILTER(
_Input,
MAP(
_Input,
fnIsWavy
)
),
_Result
)Solving the challenge of Detect Wavy Number Pattern with Python
Python solution 1 for Detect Wavy Number Pattern, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("474 Wavy Numbers.xlsx", usecols="A", nrows=10)
test = pd.read_excel("474 Wavy Numbers.xlsx", usecols="B", nrows=5)
def is_wavy(number):
digits = [int(d) for d in str(number)]
differences = [digits[i+1] - digits[i] for i in range(len(digits)-1)]
signs = [1 if diff > 0 else -1 if diff < 0 else 0 for diff in differences]
if len(signs) < 2:
return False
return all(abs(signs[i+1] - signs[i]) == 2 for i in range(len(signs)-1))
result = input[input['Numbers'].apply(is_wavy)][['Numbers']]
.rename(columns={'Numbers': 'Answer Expected'})
.reset_index(drop=True)
print(result.equals(test)) # True
Solving the challenge of Detect Wavy Number Pattern with Python in Excel
Python in Excel solution 1 for Detect Wavy Number Pattern, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_474 - Wavy Numbers.xlsx'
df1 = pd.read_excel(file_path, usecols='A')
df = pd.read_excel(file_path, usecols='B', nrows=5)
# Perform data wrangling
numbers = []
for number in df1['Numbers']:
nums = list(str(number))
if all([(nums[i - 1] < nums[i] > nums[i + 1])
or (nums[i - 1] > nums[i] < nums[i + 1])
for i in range(1, len(nums) - 1)]):
numbers.append(number)
df['My Answer'] = numbers
df
Solving the challenge of Detect Wavy Number Pattern with R
R solution 1 for Detect Wavy Number Pattern, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/474 Wavy Numbers.xlsx", range = "A1:A10")
test = read_excel("Excel/474 Wavy Numbers.xlsx", range = "B1:B6")
is_wavy <- function(number) {
digits <- str_split(as.character(number), "")[[1]] %>% as.numeric()
differences <- diff(digits)
signs <- sign(differences)
if (length(signs) < 2) {
return(FALSE)
}
all(abs(diff(signs)) == 2)
}
result = input %>%
mutate(wavy = map_lgl(Numbers, is_wavy)) %>%
filter(wavy) %>%
select(`Answer Expected` = Numbers)
identical(result, test)
# [1] TRUE
R solution 2 for Detect Wavy Number Pattern, proposed by Caroline Blake:
=LET(x,A2:A10,y,MAP(x,LAMBDA(a,LET(b,MID(a,SEQUENCE(,LEN(a)),1),c,DROP(b,,1),t,SUM(IFERROR(IF(c>b,1,0),"")),f,SUM(IFERROR(IF(c>b,0,1),"")),IF(OR(t-f>1,t-f<-1),"n","w")))),FILTER(x,y="w"))Solving the challenge of Detect Wavy Number Pattern with Excel VBA
Excel VBA solution 1 for Detect Wavy Number Pattern, proposed by Vasin Nilyok:
Sub wavyNum()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
rAns = 2
For r = 2 To LastRow
QDigit = Cells(r, 1)
nDigit = Len(QDigit)
For n = 2 To nDigit - 1
xN = Int(Mid(QDigit, n - 1, 1))
x0 = Int(Mid(QDigit, n, 1))
xP = Int(Mid(QDigit, n + 1, 1))
If x0 = xN Or x0 = xP Then
GoTo skip
ElseIf x0 < xN And x0 < xP Then
GoTo nextDigit
ElseIf x0 > xN And x0 > xP Then
GoTo nextDigit
Else
GoTo skip
End If
nextDigit:
Next n
Cells(rAns, 3) = QDigit
rAns = rAns + 1
skip:
Next r
End Sub
Excel VBA solution 2 for Detect Wavy Number Pattern, proposed by Ümit Barış Köse, MSc:
Sub numbers2()
Dim i As Long, j1 As Integer, j2 As Integer, d As Integer, Row As Long
Row = 2
Range("D2:D10").Clear
For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
s = cell.Value
d = 1
For i = 2 To Len(s)
j1 = IIf(Val(Mid(s, i, 1)) > Val(Mid(s, i - 1, 1)), 1, 0)
If (j1 = j2 And i > 2) Or Val(Mid(s, i, 1)) = Val(Mid(s, i - 1, 1)) Then d = 0
j2 = j1
Next i
If d = 1 And Len(s) > 1 Then
Cells(Row, 4).Value = s
Row = Row + 1
End If
Next cell
End Sub
Excel VBA solution 3 for Detect Wavy Number Pattern, proposed by Hiran de Silva FCMA:
Sub ExcelBI474()
Dim strDirection As String
Range("C2:C100").ClearContents
iRow = 2
iRowOutput = 2
Do While Cells(iRow, 1).Value <> ""
strThisDirection = ""
strNextDirection = ""
strYesNo = "Yes"
strSource = Cells(iRow, 1).Value
For i = 1 To Len(strSource) - 2
If Mid(strSource, i, 1) > Mid(strSource, i + 1, 1) Then
strThisDirection = "Down"
Else
strThisDirection = "Up"
End If
If Mid(strSource, i + 1, 1) > Mid(strSource, i + 2, 1) Then
strNextDirection = "Down"
Else
strNextDirection = "Up"
End If
If strThisDirection = strNextDirection Then
strYesNo = "No"
Exit For
End If
Next i
If strYesNo = "Yes" Then
Cells(iRowOutput, 3).Value = strSource
iRowOutput = iRowOutput + 1
End If
iRow = iRow + 1&
Loop
End Sub
&