Rank the students on the basis of grades. Grades are given in order as A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, F. A+ is the highest and F is the lowest grade. Rank 1 is the highest rank. Same grade will be same rank. F grade will not be ranked.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 518
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Rank by Student Grade with Power Query
Power Query solution 1 for Rank by Student Grade, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
RE = Table.AddColumn(
Source,
"Ans",
each (
if [Grades] = "F" then
""
else
List.PositionOf(List.Distinct(List.Sort(Source[Grades], {each _ & "-"})), [Grades]) + 1
)
)
in
RePower Query solution 2 for Rank by Student Grade, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Order = List.Sort(
List.Distinct(Source[Grades]),
each
if Text.Contains(_, "+") then
Text.Start(_, 1) & "1"
else if Text.Contains(_, "-") then
Text.Start(_, 1) & "3"
else
Text.Start(_, 1) & "2"
),
Lista = List.Zip({Order, {1 .. List.Count(Order) - 1} & {null}}),
Sol = Table.AddColumn(Source, "Answer", each List.ReplaceMatchingItems({[Grades]}, Lista){0})
in
SolPower Query solution 3 for Rank by Student Grade, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Sort(List.Distinct(S[Grades])),
b = List.Transform(a, each Text.Replace(Text.Replace(_, "B", "B+"), "B++", "B")),
c = Table.FromRows(List.Zip({b, {1 .. 7} & {""}})),
Sol = Table.AddColumn(
S,
"Answer Expected",
each c[Column2]{List.PositionOf(c[Column1], [Grades])}
)
in
SolSolving the challenge of Rank by Student Grade with Excel
Excel solution 1 for Rank by Student Grade, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
B2:B20&0,
IF(
g>"F",
"",
MATCH(
g,
SORT(
UNIQUE(
g
)
)
)
)
)Excel solution 2 for Rank by Student Grade, proposed by Bo Rydobon 🇹🇭:
=LET(x,XMATCH(B2:B20,{"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-"}),IFNA(MATCH(x,UNIQUE(SORT(x))),""))Excel solution 3 for Rank by Student Grade, proposed by Rick Rothstein:
=LET(u,UNIQUE(B2:B20),d,DROP(SORT(HSTACK(u,IF(LEN(u)=1,u&"B",LEFT(u)&IF(RIGHT(u)="+","A","C"))),2),-1,-1),IFNA(MATCH(B2:B20,d,0),""))Excel solution 4 for Rank by Student Grade, proposed by محمد حلمي:
=LET(b,B2:B20,i,UNIQUE(b)&",",
TEXT(XMATCH(b&",",SORTBY(i,LEFT(i),,MID(i,2,1),-1)),"[<8]0;"))Excel solution 5 for Rank by Student Grade, proposed by 🇰🇷 Taeyong Shin:
=LET(d,B2:B20,u,UNIQUE(d),IFNA(XMATCH(d,DROP(SORTBY(u,LEFT(u),,XMATCH(MID(u,2,1),{"+","","-"}),),-OR(d="F"))),""))Excel solution 6 for Rank by Student Grade, proposed by Kris Jaganah:
=LET(
a,
B2:B20,
b,
IF(
LEN(
a
)=1,
a&"*",
a
),
c,
SORTBY(
b,
LEFT(
b
),
1,
RIGHT(
b
),
-1
),
d,
XMATCH(
c,
UNIQUE(
c
)
),
e,
XLOOKUP(
b,
c,
d
),
IF(
e=MAX(
e
),
"",
e
)
)Excel solution 7 for Rank by Student Grade, proposed by Julian Poeltl:
=LET(G,B2:B20,S,SORTBY(G,LEFT(G,1),1,SWITCH(RIGHT(G,1),"+",1,"-",3,2),),U,UNIQUE(S),X,XMATCH(G,U),IF(XExcel solution 8 for Rank by Student Grade, proposed by Timothée BLIOT:
=LET(A,{"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F"},B,XLOOKUP(B2:B20,A,SEQUENCE(13)),C,MAP(B,LAMBDA(x,SUM(--(x>=UNIQUE(B))))),IF(C=MAX(C),"",C))Excel solution 9 for Rank by Student Grade, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=LET(
i,
MAP(
B2:B20,
LAMBDA(
f,
LET(
g,
TOROW(
CODE(
MID(
f,
SEQUENCE(
LEN(
f
),
,
1
),
1
)
)
),
TAKE(
TOCOL(
g
),
1
)+IFERROR(
IF(
DROP(
TOCOL(
g
),
1
)=45,
0.25,
-0.25
),
0
)
)
)
),
j,
DROP(
SORT(
UNIQUE(
i
)
),
-1
),
k,
SEQUENCE(
ROWS(
j
)
),
IFNA(
XLOOKUP(
i,
j,
k,
,
0
),
""
)
)Excel solution 10 for Rank by Student Grade, proposed by Hussein SATOUR:
=LET(
a,{"A+";
"A";
"A-";
"B+";
"B";
"B-";
"C+";
"C";
"C-";
"D+";
"D";
"D-"},g,B2:B20,IFNA(
XMATCH(
g,FILTER(
a,NOT(
ISNA(
XMATCH(
a,g
)
)
)
)
),""
)
)Excel solution 11 for Rank by Student Grade, proposed by Oscar Mendez Roca Farell:
=LET(m, XMATCH(B2:B20, TOCOL(CHAR(64+ROW(1:5))&{"+", "", "-"})), IFNA(XMATCH(m, SORT(UNIQUE(m))), ""))Excel solution 12 for Rank by Student Grade, proposed by Sunny Baggu:
=LET(
_a,
DROP(
SORT(
UNIQUE(
B2:B20
)
),
-1
),
_b,
TAKE(
SORT(
HSTACK(
_a,
LEFT(
_a
),
IF(
RIGHT(
_a
) = "+",
1,
0
)
),
{2,
3},
{1,
-1}
),
,
1
),
XLOOKUP(
B2:B20,
_b,
SEQUENCE(
ROWS(
_b
)
),
""
)
)Excel solution 13 for Rank by Student Grade, proposed by Pieter de B.:
=LET(
x,
IFNA(
XMATCH(
B2:B20,
TOCOL(
CHAR(
{65;66;67;68}
)&{"+",
"",
"-"}
)
),
),
y,
UNIQUE(
x
),
IF(
x,
MMULT(
N(
TOROW(
y
)Excel solution 14 for Rank by Student Grade, proposed by Hamidi Hamid:
=LET(
s,
IFERROR(
VLOOKUP(
B2:B20,
LET(
x,
TOCOL(
HSTACK(
CHAR(
SEQUENCE(
5,
,
65,
1
)
)&CHAR(
43
),
CHAR(
SEQUENCE(
5,
,
65,
1
)
),
CHAR(
SEQUENCE(
5,
,
65,
1
)
)&CHAR(
45
)
)
),
z,
SEQUENCE(
COUNTA(
x
)
),
HSTACK(
x,
z
)
),
2,
),
100
),
t,
UNIQUE(
LARGE(
s,
SEQUENCE(
COUNTA(
s
)
)
)
),
y,
-SORT(
-INDEX(
SEQUENCE(
COUNTA(
t
)
),
MATCH(
t,
t,
0
)
)
),
m,
HSTACK(
t,
y
),
uu,
VLOOKUP(
s,
m,
2,
0
),
IF(
B2:B20="f",
"",
uu
)
)Excel solution 15 for Rank by Student Grade, proposed by ferhat CK:
=LET(a,{"A+","A-","B+","B","C","D","D-","F"},b,SEQUENCE(,8),c,B2:B20,BYROW(c,LAMBDA(x,IF(x<>"F",XLOOKUP(x,a,b),""))))Excel solution 16 for Rank by Student Grade, proposed by Andy Heybruch:
=LET(
_grades,
B2:B20,
_all,
TOCOL(CHAR(
SEQUENCE(
4,
,
65
)
)&({"+",
"",
"-"})),
_order,
FILTER(
_all,
ISNUMBER(
XMATCH(
_all,
_grades,
0
)
)
),
IFERROR(
XMATCH(
B2:B20,
_order,
0
),
""
))Excel solution 17 for Rank by Student Grade, proposed by Bilal Mahmoud kh.:
=LET(R,LET(a,SORT(UNIQUE(REPLACE(B2:B20,2,1,LET(a,RIGHT(B2:B20,1),IF(a="+",1,IF(a="-",3,2)))))),b,REPLACE(a,2,1,CHOOSE(--RIGHT(a),"+","","-")),HSTACK(b,IF(b="F","",MATCH(b,b,0)))),VLOOKUP(B2:B20,R,2,0))Excel solution 18 for Rank by Student Grade, proposed by Imam Hambali:
=LET(
gl,
B2:B20,
gd,
TOCOL(
{"A",
"B",
"C",
"D"}&TRANSPOSE(
{"+",
"",
"-"}
),
,
1
),
su,
DROP(
SORTBY(
UNIQUE(
gl
),
XMATCH(
UNIQUE(
gl
),
gd
),
1
),
-1
),
XLOOKUP(
gl,
su,
SEQUENCE(
ROWS(
su
)
),
""
)
)Excel solution 19 for Rank by Student Grade, proposed by Imam Hambali:
=IFNA(XMATCH(B2:B20,TOCOL(XLOOKUP(TOCOL({"A";"B";"C";"D"}&{"+","","-"}),B2:B20,B2:B20),3)),"")Excel solution 20 for Rank by Student Grade, proposed by Imam Hambali:
=IFNA(XMATCH(B2:B20, DROP(SORTBY(UNIQUE(B2:B20),XMATCH(UNIQUE(B2:B20),TOCOL({"A";"B";"C";"D"}&{"+","","-"}))),-1)),"")Excel solution 21 for Rank by Student Grade, proposed by Eddy Wijaya:
=LET(
raw,B2:B20,
arr,DROP(REDUCE(0,raw,LAMBDA(a,v,
VSTACK(a,
MID(v,SEQUENCE(,2),1)))),1),
val,IFS(arr="-",-1,arr="",0,arr="+",1,TRUE,CODE(raw)),
adjArr,UNIQUE(HSTACK(raw,val)),
sortedArr,DROP(SORTBY(adjArr,CHOOSECOLS(adjArr,2),1,CHOOSECOLS(adjArr,-1),-1),-1,-2),
BYROW(raw,LAMBDA(r,IFERROR(MATCH(r,sortedArr,0),""))))Excel solution 22 for Rank by Student Grade, proposed by Mey Tithveasna:
=LET(n,XMATCH(B2:B20,{"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-"},IFNA(XMATCH(n,UNIQUE(SORT(n))),""))Excel solution 23 for Rank by Student Grade, proposed by Oscar Javier Rosero Jiménez:
=LET(g, B2:B20,
a, LEFT(g)&MID("123", 2+IF(CODE((RIGHT(g)))>45,0,CODE((RIGHT(g)))-44),1),
b, SORT(UNIQUE(a)),
IF(LEFT(a)="F","",MATCH(a,b,0)))Solving the challenge of Rank by Stude&nt Grade with Python
Python solution 1 for Rank by Student Grade, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "518 Rank Students.xlsx"
input = pd.read_excel(path, usecols="A:B")
test = pd.read_excel(path, usecols="C")
grade_levels = ["A+", "A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-"]
input['Grades'] = pd.Categorical(input['Grades'], categories=grade_levels, ordered=True)
input["rank"] = np.where(input["Grades"] == "F", np.nan, input["Grades"].rank(ascending=True, method="dense"))
print(input["rank"].equals(test["Answer Expected"])) # True
Solving the challenge of Rank by Student Grade with Python in Excel
Python in Excel solution 1 for Rank by Student Grade, proposed by Alejandro Campos:
df = xl("A1:B20", headers=True)
grade_to_rank = {'A+': 1, 'A': 2, 'A-': 3, 'B+': 4, 'B': 5, 'B-': 6, 'C+': 7, 'C': 8, 'C-': 9, 'D+': 10, 'D': 11, 'D-': 12, 'F': 13}
df['Rank'] = df['Grades'].map(grade_to_rank)
df['Answer Expected'] = df['Rank'].rank(method='dense').astype(int)
df.loc[df['Grades'] == 'F', 'Answer Expected'] = ''
df['Answer Expected']
Python in Excel solution 2 for Rank by Student Grade, proposed by Abdallah Ally:
df = xl("A1:C20", headers=True)
# Perform data wrangling
arr = ['A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F']
df['Ind'] = df['Grades'].map(
lambda x: np.nan if x == 'F' else arr.index(x)
)
df['My Answer'] = df['Ind'].rank(method='dense')
df = df[['Name', 'Grades', 'Answer Expected', 'My Answer']].fillna('')
df
Solving the challenge of Rank by Student Grade with R
R solution 1 for Rank by Student Grade, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/518 Rank Students.xlsx"
input = read_excel(path, range = "A1:B20")
test = read_excel(path, range = "C1:C20")
input$Grades <- factor(input$Grades,
levels = c("A+", "A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-"),
ordered = TRUE)
result <- input %>%
mutate(rank = ifelse(Grades == "F",
NA,
as.numeric(dense_rank(Grades))))
identical(result$rank, test$`Answer Expected`)
# [1] TRUE
&&