Hi Glide Community,
I’m working on an app to dynamically rank students based on their performance in various quizzes, where the percentile rank varies depending on the quiz difficulty and overall student performance.
Here’s what I need to achieve:
- Input Student Scores: Record each student’s score for a specific quiz in the “Student Quiz Score” table.
- Define Score Thresholds: In a separate “Quiz Score Lookup Table,” establish thresholds for each score.
- Automatic Percentile Ranking: In the “Student Quiz Score” table, automatically determine the student’s percentile rank based on their score and the relevant quiz thresholds.
My Challenge: Is it possible to create a single formula that:
- Identifies the correct row in the “Quiz Score Lookup Table” corresponding to a particular quiz, and
- Applies an If-Then-Else logic to ascertain the student’s percentile rank based on their score?
Any guidance on how to set up this formula or alternative approaches to achieve this functionality within Glide would be greatly appreciated!
Thanks in advance for your help!
Would you mind adding screen shots of the tables in question?
This will help us visualise the solution easier.
Hi @Darren_Murphy
Appreciate you asking. The tables are barebone, as I know this is the thing I will struggle with setting up, so other functionalities haven’t been built yet.
Hope the screenshots make sense.
I have a follow up question. The Top 10%, Top 20%, Top 30% values in your Quiz Percentiles table - are they just arbitrary numbers you will enter, or would you want those to be auto-calculated?
I was just doing a bit of reading on how Percentile and Percentile Rank are calculated, and I found this article that explains it quite well.
It would be quite trivial to implement both of those formulas in Glide (and you wouldn’t actually need that Quiz Percentiles table), and I can show you how to do that, but I want to be sure I’m answering the correct question first.
So can you just clarify for me please - does the article I linked to accurately describe what you want to calculate? And if yes, do you need Percentile, Percentile Rank, or both?
The linked article is exactly what we are doing.
However, the values for the thresholds have already been given to us from a much bigger data set.
So I for example have 10 test takers but we need to rank them according to the already given percentiles. We only have the 10%, 20%, etc thresholds.
In Excel I would first use xlookup to find the right quiz and then use a nested if formula like below.
=if(score>=94%,"top-10), if(score>=87%,"top-20%,…)))
I would do this for all 10 conditions.
Again, thanks for your help and hope it makes sense!
–
PS: for the avoidance of doubt this means in principle all our test takers could be top10% or bottom10% for that matter.
If you want to keep everything as is, then for each record in the Scores table, create a relation based on the quiz name to the Quizzes table.
Create multiple lookups to return top 10%, top 20% etc. Then add an If-Then-Else column to establish the “Auto score” value based on the lookup thresholds.
There might be a way with less columns, maybe have a JSON of thresholds in the Quizzes table, still do the lookup but only return the JSON, then use JavaScript to determine the Auto score based on the student score and the threshold JSON.
1 Like
Thanks @ThinhDinh - I was fearing we were on the neighborhood of a solution like that but wanted to check if there was a more elegant solution.
It seems there isn’t (if I’m mistaken please let me know), but seems the simplest (with my knowledge of Excel and no-code tools) is to use an Excel sheet as the base for this and the write the code in there. Alternatively run it through Javascript and have chat-GPT help me with the coding.
Here is my suggestion:
- Firstly, I would change your Quiz Percentiles table so that the percentile ranges are stored in a single column as a JSON structure like so:
{
"Top 10%": 96,
"Top 20%": 89,
"Top 30%": 82
}
- Then, in your Students Score table, add the following columns:
– A relation column that matches the Quiz name with the Quiz name in the Quiz Percentiles table
– A lookup that fetches the Percentile ranges via the relation column
– A JavaScript column to calculate the Auto Score for each row. The following code could be used:
const score = p1;
const ranges = JSON.parse(p2);
for (const [range, threshold] of Object.entries(ranges)) {
if (score >= threshold) {
return range;
}
}
return "Below Top 30%";
– p1 is the Students Score
– p2 is the lookup column containing the JSON
Here is how that would look:
Just one thing I’ll add:
It’s generally a good idea to avoid this if you can, because of the sync delays associated with external data sources such as Excel and Google Sheets. If you are doing calculations in an attached Excel sheet, then every time you modify data in the Glide App, you’ll need to wait anywhere between several seconds and several minutes before you see the results of the calculations reflected in the App. This can make for a pretty crappy user experience. It’s much better to do all calculations in Glide. Because Glide calculations happen on the users device, results are instant.
2 Likes
