Percentage rank...

[russtanner6]

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?

[Arthur_Tutt]

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?

[russtanner6]

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

[BillH]

@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.

[russtanner6]

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.

[Arthur_Tutt]

@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")) {

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

                await athleteTable.updateRecordsAsync(updates);
            
            count++;
            sortedAthleteHeights.pop()

        }
    }
}

 

Let me know if this works for you!