Document Pass or Fail against all students. A student is Pass if he is Pass = “Y” – Either in Maths or Science if Taken and – In Philosophy if taken and – In English if taken
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 697
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Evaluate Student Subject Status with Power Query
Power Query solution 1 for Evaluate Student Subject Status, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
"Student",
{
"Result",
each [
a = Table.AddColumn(
_,
"Abb",
each
if [Subject] = "Maths" or [Subject] = "Science" then
"M"
else
Text.Start([Subject], 1)
),
b =
if List.Count(
List.Select(
Table.Group(a, "Abb", {"All", each List.Select([Pass], (v) => v = "Y"){0}? ?? "N"})[
All
],
(w) => w = "N"
)
)
> 0
then
"Fail"
else
"Pass"
][b]
}
),
C = Table.Sort(B, "Student")
in
CPower Query solution 2 for Evaluate Student Subject Status, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.Sort(Table.Group(Source, "Student", {"Result", Fun}), "Student"),
Fun = each [
A = Table.SelectRows(_, (x) => x[Pass] = "N")[Subject],
B = List.ContainsAny(A, {"Philosophy", "English"}) or List.ContainsAll(A, {"Science", "Math"}),
C = if B then "Fail" else "Pass"
][C]
in
ResPower Query solution 3 for Evaluate Student Subject Status, proposed by Antriksh Sharma:
let
Source = Table,
Replace = (T, L) =>
let
a = Table.SelectRows(T, each List.Contains(L, [Subject])),
b = List.ReplaceMatchingItems(a[Pass], {{"Y", 1}, {"N", 0}}),
c = List.Max(b)
in
c ?? 1,
Group = Table.Group(
Source,
"Student",
{
"Result",
each {"Fail", "Pass"}{
Replace(_, {"Maths", "Science"}) * Replace(_, {"Philosophy"}) * Replace(_, {"English"})
},
type text
}
),
Sort = Table.Sort(Group, {"Student", Order.Ascending})
in
SortPower Query solution 4 for Evaluate Student Subject Status, proposed by Peter Krkos:
let a = Table.SelectRows(_, (x)=> List.Contains({"Maths", "Science"}, x[Subject]))[Pass]
in {"Fail", "Pass"}{Byte.From((if List.IsEmpty(a) then true else List.Contains(a, "Y")) and not List.Contains(Table.SelectRows(_, (x)=> List.Contains({"Philosophy", "English"}, x[Subject]))[Pass], "N"))},
type text}}), "Student")
Power Query solution 5 for Evaluate Student Subject Status, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = {"Maths", "Science"},
L = Table.ReplaceValue(H, "N", "Result", Replacer.ReplaceText, {"Pass"}),
C = Table.Pivot(
L,
{"Result"},
"Pass",
"Subject",
each {"Pass", "Fail"}{
Byte.From(List.ContainsAll(_, P) or not List.IsEmpty(List.RemoveItems(_, P)))
}
)
in
CPower Query solution 6 for Evaluate Student Subject Status, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
lstSTEM = {"Maths", "Science"},
lstOther = {"Philosophy", "English"},
A = Table.AddColumn(
source,
"SubjectGroups",
each if List.Contains(lstSTEM, [Subject]) then "STEM" else "OTHER",
type text
),
B = Table.Group(
A,
{"Student", "SubjectGroups"},
{
{"CountAll", each Table.RowCount(_), Int64.Type},
{"CountPass", each Table.RowCount(Table.SelectRows(_, each [Pass] = "Y")), Int64.Type}
}
),
C = Table.AddColumn(
B,
"Result",
each
if [SubjectGroups] = "STEM" then
if [CountPass] > 0 then "Pass" else "Fail"
else if [CountPass] = [CountAll] then
"Pass"
else
"Fail"
),
D = Table.Group(
C,
{"Student"},
{"Result", each if not (List.Contains([Result], "Fail")) then "Pass" else "Fail"}
)
in
DPower Query solution 7 for Evaluate Student Subject Status, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
R = Table.Sort(
Table.Group(
Source,
"Student",
{
"Result",
each [
a = Table.SelectRows(_, (p) => p[Pass] = "N")[Subject],
b = {"Pass", "Fail"}{
Byte.From(
List.ContainsAny(a, {"Philosophy", "English"})
or List.ContainsAll(a, {"Science", "Math"})
)
}
][b]
}
),
"Student"
)
in
RPower Query solution 8 for Evaluate Student Subject Status, proposed by Ezel K.:
let
Kaynak = Excel.CurrentWorkbook(){[Name="Tablo1"]}[Content],
#"Değiştirilen Değer" = Table.ReplaceValue(Kaynak,"Science","Maths",Replacer.ReplaceText,{"Subject"}),
#"Birleştirilen Sütunlar" = Table.CombineColumns(#"Değiştirilen Değer",{"Subject", "Pass"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Birleştirilmiş"),
#"Kaldırılan Yinelenenler" = Table.Distinct(#"Birleştirilen Sütunlar"),
#"Filtrelenen Satırlar" = Table.SelectRows(#"Kaldırılan Yinelenenler", each ([Birleştirilmiş] <> "Maths:N")),
#"Gruplanan Satırlar" = Table.Group(#"Filtrelenen Satırlar", {"Student"}, {{"Sayı", each Text.Combine([Birleştirilmiş]," / "), type text}}),
#"Koşullu Sütun Eklendi" = Table.AddColumn(#"Gruplanan Satırlar", "Özel", each if Text.Contains([Sayı], ":N") then "Fail" else "Pass"),
#"Kaldırılan Sütunlar" = Table.RemoveColumns(#"Koşullu Sütun Eklendi",{"Sayı"}),
#"Sıralanan Satırlar" = Table.Sort(#"Kaldırılan Sütunlar",{{"Student", Order.Ascending}}),
Result = Table.RenameColumns(#"Sıralanan Satırlar",{{"Özel", "Result"}})
in
Result
Show translation
Show translation of this commentSolving the challenge of Evaluate Student Subject Status with Excel
Excel solution 1 for Evaluate Student Subject Status, proposed by 🇰🇷 Taeyong Shin:
=GROUPBY(A3:A18,
C3:C18&B3:B18,
LAMBDA(x,
IF(SUM({0.1;0.1;1;1}*(XLOOKUP(
{"m";"s";"p";"e"},
x,
RIGHT(
x
),
"Y",
1
)="Y"))>2,
"Pass",
"Fail")),
,
0)Excel solution 2 for Evaluate Student Subject Status, proposed by Alejandro Campos:
=LET(
d, A3:C18,
s, UNIQUE(FILTER(INDEX(d, , 1), INDEX(d, , 1) <> "")),
g, LAMBDA(n,
LET(
r, FILTER(d, INDEX(d, , 1) = n),
p, INDEX(r, , 2),
b, INDEX(r, , 3),
m, SUM((p = "Y") * ISNUMBER(MATCH(b, {"Maths", "Science"}, 0))),
ph, IFERROR(INDEX(p, XMATCH("Philosophy", b)), "Y") = "Y",
en, IFERROR(INDEX(p, XMATCH("English", b)), "Y") = "Y",
IF(AND(m, ph, en), "Pass", "Fail"))),
st, BYROW(s, LAMBDA(n, g(n))),
SORT(HSTACK(s, st)))Excel solution 3 for Evaluate Student Subject Status, proposed by Timothée BLIOT:
=LET(A,A3:A18,B,B3:B18,C,C3:C18,GROUPBY(A,MAP(A,B,C,LAMBDA(x,y,z,IF(OR(z="Maths",z="Science"),SUM(--(FILTER(B,(A=x)*((C="Science")+(C="Maths")),0)="Y")),--(y="Y")))),LAMBDA(x,IF(PRODUCT(x),"Pass","Fail")),,0))Excel solution 4 for Evaluate Student Subject Status, proposed by Hussein SATOUR:
=LET(
f,
LAMBDA(
t,
x,
INDEX(
t,
,
x
)
),
a,
SORT(
HSTACK(
A3:B18,
SUBSTITUTE(
C3:C18,
"Science",
"Maths"
)
),
2,
-1
),
b,
DROP(
PIVOTBY(
f(
a,
1
),
XLOOKUP(
f(
a,
1
)&f(
a,
3
),
f(
a,
1
)&f(
a,
3
),
f(
a,
2
)
),
f(
a,
1
),
COUNTA,
,
0
),
1,
-2
),
IFS(
ISNUMBER(
b
),
"Fail",
b="",
"Pass",
1,
b
)
)Excel solution 5 for Evaluate Student Subject Status, proposed by Duy Tùng:
=LET(I,
INDEX,
a,
A3:A18,
b,
B3:B18="y",
c,
C3:C18,
d,
(c="Maths")+(c="Science"),
e,
(c="English")+(c="Philosophy"),
u,
GROUPBY(
a,
HSTACK(
e,
e*b,
d,
d*b
),
SUM,
,
0
),
HSTACK(TAKE(
u,
,
1
),
IF((I(
u,
,
2
)=I(
u,
,
3
))+(I(
u,
,
4
)<=I(
u,
,
5
)+1)>1,
"Pass",
"Fail")))Excel solution 6 for Evaluate Student Subject Status, proposed by Md. Zohurul Islam:
=LET(
st,
A3:A18,
dt,
B3:C18,
u,
VSTACK(
"Philosophy",
"English"
),
v,
VSTACK(
"Maths",
"Science"
),
f,
LAMBDA(
x,
y,
FILTER(
TAKE(
x,
,
1
),
ISNUMBER(
XMATCH(
DROP(
x,
,
1
),
y
)
),
0
)="Y"
),
z,
DROP(
REDUCE(
"",
SORT(
UNIQUE(
st
)
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
dt,
st=y
),
b,
AND(
f(
a,
u
)
),
c,
OR(
f(
a,
v
)
),
d,
IF(
AND(
b,
c
),
"Pass",
"Fail"
),
e,
VSTACK(
x,
HSTACK(
y,
d
)
),
e
)
)
),
1
),
z
)Excel solution 7 for Evaluate Student Subject Status, proposed by Asheesh Pahwa:
=REDUCE(
E2:F2,
SORT(
UNIQUE(
A3:A18
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B3:C18,
A3:A18=y
),
t,
TAKE(
f,
,
1
),
_t,
TAKE(
f,
,
-1
),
I,
IFS(
_t="Maths",
"Y",
_t="Science",
"Y",
TRUE,
t
),
m,
XMATCH(
_t,
{"Maths";"Science";"Philosophy";"English"}
),
p(
I="Y"
)*m,
HSTACK(
y,
IF(
AND(
SIGN(
p
)
),
"Pass",
"Fail"
)
)
)
)
)
)Excel solution 8 for Evaluate Student Subject Status, proposed by Dhaval Patel:
=SORT(
UNIQUE(
$A$3:$A$18
)
)
Formula for cell F3,
=IF(AND( IF((COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Maths"
) + COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Science"
)) > 0,
(COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Maths",
$B$3:$B$18,
"Y"
) + COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Science",
$B$3:$B$18,
"Y"
)) >= 1,
TRUE),
IF(
COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"English"
) > 0,
COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"English",
$B$3:$B$18,
"Y"
) >= 1,
TRUE
),
IF(
COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Philosophy"
) > 0,
COUNTIFS(
$A$3:$A$18,
E3,
$C$3:$C$18,
"Philosophy",
$B$3:$B$18,
"Y"
) >= 1,
TRUE
) ),
"Pass",
"Fail")Excel solution 9 for Evaluate Student Subject Status, proposed by ferhat CK:
=LET(a,DROP(PIVOTBY(A3:A18,B3:B18,C3:C18,ARRAYTOTEXT,,0,,0),1,-1),SORT(HSTACK(TAKE(a,,1),IF(REGEXTEST(TAKE(a,,-1),"Philosophy|English"),"Fail","Pass"))))Excel solution 10 for Evaluate Student Subject Status, proposed by Erdit Qendro:
=LET(
gC,
{"Maths",
"Science"},
g,
IF(
ISNUMBER(
XMATCH(
C3:C18,
gC,
0
)
),
"MS",
C3:C18
),
pf,
IF(
B3:B18="Y",
1,
0
),
pv,
PIVOTBY(
A3:A18,
g,
pf,
MAX,
,
0,
,
0,
),
HSTACK(
TAKE(
pv,
1-ROWS(
pv
),
1
),
IF(
BYRO&W(
DROP(
pv,
1,
1
),
MIN
),
"Pass",
"Fail"
)
)
)Excel solution 11 for Evaluate Student Subject Status, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
p,
PIVOTBY(
A3:A18,
C3:C18,
B3:B18,
CONCAT,
,
0,
,
0
),
r,
IF(
DROP(
p,
1,
1
)="N",
0,
1
),
s,
IF(
MMULT(
r,
{2;1;2;1}
)>4,
"Pass",
"Fail"
),
HSTACK(
DROP(
TAKE(
p,
,
1
),
1
),
s
)
)
=LET(
p,
PIVOTBY(
A3:A18,
C3:C18,
B3:B18,
SINGLE,
,
0,
,
0
),
r,
BYROW(
IF(
p="",
"Y",
p
),
LAMBDA(
x,
AND(
OR(
INDEX(
x,
XMATCH(
{"Maths",
"Science"},
TAKE(
p,
1
),
0
)
)="Y"
),
AND(
INDEX(
x,
XMATCH(
{"English",
"Philosophy"},
TAKE(
p,
1
),
0
)
)="Y"
)
)
)
),
VSTACK(
{"Result",
"Result"},
DROP(
HSTACK(
TAKE(
p,
,
1
),
IF(
r,
"Pass",
"Fail"
)
),
1
)
)
)
=LET(st,
UNIQUE(
SORT(
A3:A18
)
),
su,
TOROW(
SORT(
UNIQUE(
C3:C18
)
)
),
m,
st&","&su,
f,
--(MAP(m,
LAMBDA(x,
FILTER($B$3:$B$18,
($A$3:$A$18=TEXTBEFORE(
x,
","
))*($C$3:$C$18=TEXTAFTER(
x,
","
)),
"Y")))="Y"),
C,
LAMBDA(
x,
CHOOSECOLS(
f,
XMATCH(
x,
su,
0
)
)
),
VSTACK({"Result",
"Result"},
HSTACK(st,
IF((C(
"Maths"
)+C(
"Science"
))*C(
"English"
)*C(
"Philosophy"
),
"Pass",
"Fail"))))Excel solution 12 for Evaluate Student Subject Status, proposed by Fredson Alves Pinho:
=LET(x,PIVOTBY(A3:A18,SUBSTITUTE(C3:C18,"Science","Maths"),B3:B18="Y",OR,,0,,0),DROP(IF({1,0},x,IF(BYROW(x,AND),"Pass","Fail")),1,-2))Excel solution 13 for Evaluate Student Subject Status, proposed by Craig Runciman:
=LET(
a,
GROUPBY(
HSTACK(
A3:A18,
SUBSTITUTE(
C3:C18,
"Science",
"Maths"
)
),
B3:B18,
LAMBDA(
a,
SUM(
IF(
a="Y",
1,
0
)
)
),
,
0
),
r,
GROUPBY(
INDEX(
a,
,
1
),
INDEX(
a,
,
3
),
LAMBDA(
z,
IF(
MIN(
z
)=1,
"Pass",
"Fail"
)
),
,
0
),
r
)Excel solution 14 for Evaluate Student Subject Status, proposed by red craven:
=LET(
e,
C3:C18,
p,
DROP(
PIVOTBY(
A3:A18,
IF(
REGEXTEST(
e,
"M|S"
),
"Z",
e
),
B3:B18="Y",
OR,
,
0
),
1,
-1
),
HSTACK(
TAKE(
p,
,
1
),
IF(
BYROW(
DROP(
p,
,
1
),
AND
),
"Pass",
"Fail"
)
)
)Solving the challenge of Evaluate Student Subject Status with Python in Excel
Python in Excel solution 1 for Evaluate Student Subject Status, proposed by Alejandro Campos:
data = xl("A2:C18", headers=True)
s = {}
for stu, p, sub in zip(data["Student"], data["Pass"], data["Subject"]):
s.setdefault(stu, {"Maths_or_Science": None, "Philosophy": None, "English": None})
if sub in ["Maths", "Science"]:
s[stu]["Maths_or_Science"] = p == 'Y' or s[stu]["Maths_or_Science"]
elif sub in s[stu]:
s[stu][sub] = p == 'Y'
df = pd.DataFrame([{"Student": k, "Status": "Pass" if all(v is not False for v in d.values()) else "Fail"} for k,
d in s.items()]).sort_values("Student").reset_index(drop=True)
Python in Excel solution 2 for Evaluate Student Subject Status, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:C18", True)
pivot = df.pivot(index="Student", columns="Subject", values="Pass").fillna("NA")
result = (
((pivot["Maths"] == "Y") | (pivot["Science"] == "Y"))
& (pivot["Philosophy"] != "N")
& (pivot["English"] != "N")
)
result = result.map({True: "Pass", False: "Fail"}).reset_index(name="Result")
Solving the challenge of Evaluate Student Subject Status with R
R solution 1 for Evaluate Student Subject Status, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/696 Pass or Fail.xlsx"
input = read_excel(path, range = "A2:C18")
test = read_excel(path, range = "E3:F7", col_names = c("Student", "Result"))
result = input %>%
pivot_wider(names_from = Subject, values_from = Pass, values_fill = "Y") %>%
mutate(Result = if_else((Maths == "Y" | Science == "Y") & English == "Y" & Philosophy == "Y",
"Pass", "Fail")) %>%
select(Student, Result) %>%
arrange(Student)
all.equal(result, test)
&&