Generate X and Y axes of all 4 quadrants for value given in A1. Example shown is for value 4.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 389
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Axis for Four Quadrants with Power Query
Power Query solution 1 for Generate Axis for Four Quadrants, proposed by John V.:
let
n = 4, a = 1 + n, b = 1 + 2 * n, l = {1..b},
R = List.TransformMany(l, each l, (r, c) => if r = a then c - a else if c = a then a - r else null)
in
Table.FromRows(List.Split(R, b))
Blessings!
Power Query solution 2 for Generate Axis for Four Quadrants, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
A = 4,
B = List.Reverse({- A .. A}),
C = List.Count(B),
D = List.Repeat({null}, Number.RoundDown(C / 2)),
E = Table.FromRows(List.Transform(B, each if _ = 0 then List.Reverse(B) else D & {_} & D))
in
EPower Query solution 3 for Generate Axis for Four Quadrants, proposed by Luan Rodrigues:
let
Fonte = 4,
res = let
a = {-Fonte..Fonte},
b = Table.Combine(List.Transform(List.ReplaceValue(a,0,List.Reverse(a),Replacer.ReplaceValue), each if _ is list then Table.FromRows({_}) else hashtag#table({"Column"& Text.From(Fonte+1) },{{_}}))),
c = Table.SelectColumns(b,List.Sort(Table.ColumnNames(b), each Number.From(Text.Select(_,{"0".."9"})) ))
in
c
in
res
Power Query solution 4 for Generate Axis for Four Quadrants, proposed by Bhavya Gupta:
let
Source = 4,
Axis = {- Source .. Source},
Output = Table.FromColumns(
List.Transform(
Axis,
each if _ = 0 then List.Reverse(Axis) else List.Repeat({null}, Source) & {_}
)
)
in
OutputPower Query solution 5 for Generate Axis for Four Quadrants, proposed by Ramiro Ayala Chávez:
let
N = 4,
a = List.Repeat({null}, 2 * N),
b = List.RemoveLastN(List.Repeat(List.Split(a, N), N), N),
c = Table.AddIndexColumn(Table.FromColumns(b), "I"),
d = Table.AddIndexColumn(
Table.FromRows(Table.ToColumns(Table.TransformColumns(c, {"I", each _ - N})) & b),
"J"
),
e = List.Transform(
Table.ToColumns(Table.TransformColumns(d, {"J", each _ - N})),
each List.Reverse(_)
),
f = List.Transform(List.Reverse(List.RemoveLastN(e)), each List.Transform(_, each Number.Abs(_))),
Sol = Table.FromColumns(e & f)
in
SolSolving the challenge of Generate Axis for Four Quadrants with Excel
Excel solution 1 for Generate Axis for Four Quadrants, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
SEQUENCE(
,
A1*2+1,
-A1
),
t,
TOCOL(
s
),
IF(
s*t,
"",
s-t
)
)Excel solution 2 for Generate Axis for Four Quadrants, proposed by Rick Rothstein:
=LET(
a,
A1+1,
b,
a+A1,
MAKEARRAY(
b,
b,
LAMBDA(
r,
c,
IF(
r=a,
c-a,
IF(
c=a,
a-r,
""
)
)
)
)
)Excel solution 3 for Generate Axis for Four Quadrants, proposed by John V.:
=LET(
n,
1+A1,
r,
SEQUENCE(
2*n-1
),
c,
TOROW(
r
),
IFS(
r=n,
c-n,
c=n,
n-r,
1,
""
)
)Excel solution 4 for Generate Axis for Four Quadrants, proposed by محمد حلمي:
=LET(
i,
A1,
v,
i*2+1,
MAKEARRAY(
v,
v,
LAMBDA(
r,
c,
IF(
c-1=i,
c-r,
""
)
)
)
)
=LET(
i,
A1,
v,
i*2+1,
MAKEARRAY(
v,
v,
LAMBDA(
r,
c,
IF(
r-1=i,
c-r,
""
)
)
)
)Excel solution 5 for Generate Axis for Four Quadrants, proposed by Timothée BLIOT:
=LET(A,A1+1,B,A+A1,MAKEARRAY(B,B,LAMBDA(x,y,SWITCH(TRUE,y=A,A-x,x=A,A-y,""))))Excel solution 6 for Generate Axis for Four Quadrants, proposed by Hussein SATOUR:
=LET(a,
A1*2+1,
S,
SEQUENCE(
a^2
),
Sinv,
SEQUENCE(
a^2,
,
a^2,
-1
),
c,
FILTER(Sinv-S,
(MOD(
Sinv-S,
a
)=0) + (ABS(
Sinv-S
)Excel solution 7 for Generate Axis for Four Quadrants, proposed by Sunny Baggu:
=LET(
n,
4,
r,
SEQUENCE(
2 * n + 1
),
c,
TOROW(
r
),
v,
c - r,
IF(
MOD(
r,
n + 1
) * MOD(
c,
n + 1
) = 0,
v,
""
)
)Excel solution 8 for Generate Axis for Four Quadrants, proposed by Abdallah Ally:
=LET(
a,
A1,
b,
EXPAND(
"",
a,
a,
""
),
c,
SEQUENCE(
a
),
d,
SORT(
c,
,
-1
),
VSTACK(
HSTACK(
b,
d,
b
),
HSTACK(
-TOROW(
d
),
0,
TOROW(
c
)
),
HSTACK(
b,
c,
b
)
)
)Excel solution 9 for Generate Axis for Four Quadrants, proposed by 🇵🇪 Ned Navarrete C.:
=c-1,
a=r-1),
c-r,
""))))Excel solution 10 for Generate Axis for Four Quadrants, proposed by Pieter de B.:
=LET(
v,
SEQUENCE(
A1*2+1,
,
A1,
-1
),
h,
TOROW(
v
),
IF(
v=0,
-h,
IF(
h,
"",
v
)
)
)Excel solution 11 for Generate Axis for Four Quadrants, proposed by Milan Shrimali:
=IFERROR(
HSTACK(
VSTACK(
SUBSTITUTE(
LEFT(
0&SEQUENCE(
A1
),
1
),
0,
""
),
DROP(
SORT(
TRANSPOSE(
SORT(
SEQUENCE(
A1+1,
1,
0,
-1
),
,
-1,
FALSE
)
),
,
1,
TRUE
),
,
-1
)
),
VSTACK(
SORT(
SEQUENCE(
A1,
1,
1,
1
),
,
-1
),
HSTACK(
SEQUENCE(
A1+1,
1,
0,
-1
),
TRANSPOSE(
SEQUENCE(
A1,
1,
1,
1
)
)
)
)
),
""
)Excel solution 12 for Generate Axis for Four Quadrants, proposed by Ziad A.:
=LET(
n,
A1*2+1,
MAKEARRAY(
n,
n,
LAMBDA(
i,
j,
IFS(
i=A1+1,
j+A1-n,
j=A1+1,
n-i-A1,
1,
)
)
)
)Excel solution 13 for Generate Axis for Four Quadrants, proposed by Giorgi Goderdzishvili:
=LET(
_n,
A1,
_mk,
MAKEARRAY(2*_n+1,
2*_n+1,
LAMBDA(r,
c,
IF(c=(_n+1),
_n+1-r,
IF(r=(_n+1),
c-(_n+1),
"")))),
_mk)Excel solution 14 for Generate Axis for Four Quadrants, proposed by Anup Kumar:
=LET(
nm,
A1,
ps,
nm+1,
tl,
nm*2+1,
MAKEARRAY(
tl,
tl,
LAMBDA(
r,
c,
IFS(
r=ps,
c-ps,
c=ps,
ps-r,
TRUE,
""
)
)
)
)Excel solution 15 for Generate Axis for Four Quadrants, proposed by Andres Rojas Moncada:
=LET(v,
B1,
MAKEARRAY(v*2+1,
v*2+1,
LAMBDA(f,
c,
IFS(c=v+1,
(v+1)-f,
f=v+1,
c-(v+1),
TRUE,
""))))
Otra forma podria ser:
=LET(
v,
A1,
MAKEARRAY(
v*2+1,
v*2+1,
LAMBDA(
f,
c,
IF(
OR(
c=v+1,
f=v+1
),
c-f,
""
)
)
)
)Excel solution 16 for Generate Axis for Four Quadrants, proposed by Fábio Gatti:
=LAMBDA(
Value,
LET(
_Base,
OFFSET(
INDIRECT(
CELL(
"endereço"
)
),
Value,
Value
),
_Arr,
MAKEARRAY(
Value*2+1,
Value*2+1,
LAMBDA(
a,
b,
IFS(
a&b="11",
Value,
a=Value+1,
b-Value-1,
b=Value+1,
a-Value-1,
1,
""
)
)
),
_Arr
)
)(4)Excel solution 17 for Generate Axis for Four Quadrants, proposed by Josh Brodrick:
=MAKEARRAY((A1*2)+1,
(A1*2)+1,
LAMBDA(
x,
y,
IF(
OR(
y=A1+1,
x=A1+1
),
-x+y,
""
)
))Excel solution 18 for Generate Axis for Four Quadrants, proposed by Tyler Cameron:
=LET(
a,
A1+1,
b,
A1*2+1,
MAKEARRAY(
b,
b,
LAMBDA(
r,
c,
IF(
c=a,
a-r,
IF(
r=a,
c-a,
""
)
)
)
)
)Excel solution 19 for Generate Axis for Four Quadrants, proposed by Narayanan J 🇮🇳:
=LET(
cll,
A1,
l,
cll*2+1,
MAKEARRAY(
l,
l,
LAMBDA(
r,
c,
IF(
OR(
r=cll+1,
c=cll+1
),
c-r,
""
)
)
)
)Solving the challenge of Generate Axis for Four Quadrants with R
R solution 1 for Generate Axis for Four Quadrants, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
number = read_excel("Excel/389 Quadrant.xlsx", range = "A1", col_names = FALSE) %>% pull()
test = read_excel("Excel/389 Quadrant.xlsx", range = "A2:I10", col_names = FALSE) %>%
as.data.frame()
colnames(test) = c(as.character(1:ncol(test)))
generate_cross = function(size) {
full_size = 2*size+1
center = size+1
mat = matrix(NA, full_size, full_size)
seq = seq(size, -size)
rev_seq = rev(seq)
mat[center,] <- rev_seq
mat[,center] <- seq
mat = as.data.frame(mat)
colnames(mat) = c(as.character(1:ncol(mat)))
return(mat)
}
result = generate_cross(number)
&&&