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: 696
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Evaluate Pass Based on Subjects with Power Query
Power Query solution 1 for Evaluate Pass Based on Subjects, 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 Pass Based on Subjects, 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 Pass Based on Subjects, 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 Pass Based on Subjects, 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 Pass Based on Subjects, 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 Pass Based on Subjects, 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
DSolving the challenge of Evaluate Pass Based on Subjects with Excel
Excel solution 1 for Evaluate Pass Based on Subjects, proposed by Bo Rydobon 🇹🇭:
=GROUPBY(A3:A18,B3:B18&C3:C18,LAMBDA(x,LET(R,REGEXTEST,c,CONCAT(x),IF((R(c,"[^MS]")*R(c,"YM|YS"))*(1-R(c,"NP|NE")),"Pass","Fail"))),,0)Excel solution 2 for Evaluate Pass Based on Subjects, 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 3 for Evaluate Pass Based on Subjects, proposed by Kris Jaganah:
=GROUPBY(A3:A18,LEFT(C3:C18)&B3:B18,LAMBDA(x,LET(a,LEFT(x),b,RIGHT(x),c,(a="M")+(a="S"),d,MAX((b="Y")*c),e,a="P",f,MIN(IF(e,e*(b="Y"),1)),g,a="E",h,MIN(IF(g,g*(b="Y"),1)),IF(MIN(d,f,g),"Pass","Fail"))),,0)Excel solution 4 for Evaluate Pass Based on Subjects, 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 5 for Evaluate Pass Based on Subjects, 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 6 for Evaluate Pass Based on Subjects, 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 7 for Evaluate Pass Based on Subjects, proposed by Oscar Mendez Roca Farell:
=LET(I,
INDEX,
p,
DROP(
PIVOTBY(
A3:A18,
C3:C18,
N(
B3:B18="Y"
),
SUM,
,
0,
,
0
),
1
),
m,
IF(
p="",
1,
p
),
HSTACK(TAKE(
m,
,
1
),
IF((I(
m,
,
3
)+I(
m,
,
5
))*I(
m,
,
2
)*I(
m,
,
4
),
"Pass",
"Fail")))Excel solution 8 for Evaluate Pass Based on Subjects, 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 9 for Evaluate Pass Based on Subjects, proposed by Sunny Baggu:
=LET(
_u, SORT(UNIQUE(A3:A18)),
_r, MAP(
_u,
LAMBDA(a,
LET(
_f, FILTER(B3:C18, A3:A18 = a),
_c, XLOOKUP(
{"Science", "Maths", "Philosophy", "English"},
TAKE(_f, , -1),
TAKE(_f, , 1),
"Y"
),
AND(OR(TAKE(_c, , 2) = "Y"), AND(TAKE(_c, , -2) = "Y"))
)
)
),
HSTACK(_u, IF(_r, "Pass", "Fail"))
)Excel solution 10 for Evaluate Pass Based on Subjects, 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 11 for Evaluate Pass Based on Subjects, 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 12 for Evaluate Pass Based on Subjects, 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(BYROW(DROP(pv,1,1),MIN),"Pass","Fail")))Excel solution 13 for Evaluate Pass Based on Subjects, 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 14 for Evaluate Pass Based on Subjects, 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 Pass Based on Subjects with Python
Python solution 1 for Evaluate Pass Based on Subjects, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "696 Pass or Fail.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=17)
test = pd.read_excel(path, usecols="E:F", skiprows=1, nrows=5, names=["Student", "Result"]).sort_values("Student").reset_index(drop=True)
result = pd.pivot_table(input, index="Student", columns="Subject", values="Pass", fill_value="Y", aggfunc='first')
result = result.reset_index()
result = result.assign(Result=lambda df: ["Pass" if ((row["Maths"] == "Y" or row["Science"] == "Y") and row["English"] == "Y" and row["Philosophy"] == "Y") else "Fail" for _, row in df.iterrows()])
result = result[["Student", "Result"]].sort_values("Student").reset_index(drop=True)
result.index.name = None
print(result.equals(test)) # True
Python solution 2 for Evaluate Pass Based on Subjects, proposed by Claudiu B.:
import pandas as pd
df = pd.read_excel(r"students_failed-taken.xlsx")
p = df.pivot(index='Student', columns='Subject', values='Pass').fillna('N/A').reset_index()
def check_result(English, Maths, Philosophy, Science):
if (Maths=='Y' or Science=='Y') and Philosophy!='N' and English!='N':
return 'Pass'
else:
return 'Fail'
p['Result'] = p.apply(lambda x: check_result(x['English'], x['Maths'], x['Philosophy'], x['Science']), axis=1)
output = p.loc[:, ['Student', 'Result']]
export = output.to_excel("final_result.xlsx", index=None)
export
Solving the challenge of Evaluate Pass Based on Subjects with Python in Excel
Python in Excel solution 1 for Evaluate Pass Based on Subjects, 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 Pass Based on Subjects, 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 Pass Based on Subjects with R
R solution 1 for Evaluate Pass Based on Subjects, 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)
&&