# Percentage rank...

Topic Labels: Formulas
1038 5
cancel
Showing results for
Did you mean:
4 - Data Explorer

I'm creating an athlete database. I want to create a column that shows where an athlete ranks, for various metrics, relative to everyone else. For example, let's say someone is 6'9" tall and that puts the athlete in the top 2% of all the athletes in the database. How can I calculate that for that athlete (and every other athlete)? Is that possible?

5 Replies 5
8 - Airtable Astronomer

Hey @russtanner6 this could be done with some advanced math and an automation. Are you familiar with calculating a normal distribution?

Essentially you'd need a script to read in all the heights (for this example), calculate the normal distribution, then calculate where each athlete falls on that distribution, and output the result.

What's your background with probability and statistics?

4 - Data Explorer

Not much experience. But I'll see if ChatGPT can put something together. Thanks for the start.

@russtanner6 I looked at several possibilities but don't see anything that doesn't involve outside tools, which is disappointing since I was able to quickly do things like this with more primitive tools (like the dreaded ms-access) years ago.  I would definitely be interested in hearing if you make any progress with ChapGPT.  Thanks and good luck.

4 - Data Explorer

That's surprising. It seems like Airtable should be able to do that. I tried using ChatGPT but it basically gave the same answer you did, saying it was a manual process and there's not really a great solution.

8 - Airtable Astronomer

@russtanner6  @BillH  got some good news for you - had some time to crunch though this and came up with a simple automation you can implement. Here's a quick Loom Video to show you step by step exactly how it works.

Table setup and expected output:

Automation Setup:

Automation Script:

*** Remember to change all my table / field names to match yours to make this work ***

``````// Get Heights Table
let athleteTable = base.getTable("Heights");
let atheleteQuery = await athleteTable.selectRecordsAsync({fields: ["Height", "Record ID"]})
let athleteRecords = atheleteQuery.records;
console.log(athleteRecords)
console.log(athleteRecords[0])
console.log(athleteRecords[0].getCellValue("Height"))

let allAthleteHeights = [];
let sortedAthleteHeights = [];

for (let i = 0; i < athleteRecords.length; i++) {
allAthleteHeights.push(athleteRecords[i].getCellValue("Height"))
}

console.log(allAthleteHeights)

sortedAthleteHeights = allAthleteHeights.sort(function(a, b){return a- b});

console.log(sortedAthleteHeights)

let count = 1;

for (let j = sortedAthleteHeights.length; j >= 0; j--) {
for (let k = 0; k < athleteRecords.length; k++) {
if (sortedAthleteHeights[j] == athleteRecords[k].getCellValue("Height")) {

"id": athleteRecords[k].getCellValue("Record ID"),
fields: {
"Rank": count,
"% (Rank)": (count / athleteRecords.length)
}
}]