Re: Percentiles calculation

Sean_Ryefield · ‎Jul 09, 2020

I was wondering if there is a way to do a percentile calculation in Airtable.

Basically I’m trying to determine salary percentiles (in my case let’s say 75th percentile).

So say I have a column of salaries in a “Positions” table with 25 rows (mine has way more, but for the sake of simplicity).

I figured out that I can do the linked record trick to get all the values pulled into a single cell in another table, call it “Summary”. So i would use a lookup field to pull all 25 salaries from “Positions” into an array in the other table. I can then do a count of the number of values in that array. Then in theory I would multiply that count times my percentile (0.75 in my example), and round the result up to an integer, n. Then it’s a matter of finding that nth value in the array. BUT the array isn’t in order, so without being able to sort the array in ascending order, I can’t actually do the percentile.

Anyone have any experience with this type of problem and a possible solution? I’m out of ideas at this point.

JonathanBowen · ‎Jul 10, 2020

Hi @Sean_Ryefield - there might be a convoluted way to do this with links, lookups and so on, but this is really a task for an Airtable script. I did a write up of a similar thing here. It wouldn’t be too far from this to a percentiles calculation.

JonathanBowen · ‎Jul 10, 2020

This might do the trick. Put together quickly, so might not be fully accurate, but feel free to test it out.

On a table like this:

Using this script:

let table = base.getTable('Table 1');
let query = await table.selectRecordsAsync();
let k = 0.75;
let salaries = query.records.map(record => {
    return record.getCellValue('Salary')
})

let ordered_salaries = salaries.sort();

let n = ordered_salaries.length;
let index = Math.ceil(k * n);
let result;
if (index != (k * n)) {
    result = ordered_salaries[index - 1];
} else {
    result = (ordered_salaries[index - 1] + ordered_salaries[index]) / 2;
}

console.log(index, k * n)
output.text(`The ${k} percentile is ${result}`);

This is following the method described here.

The if/else part of the script is deal with the 4a/4b steps outlined in the method.

JonathanBowen · ‎Jul 10, 2020

I posted a more detailed breakdown of the script here: