Find the median average from a rollup field

[Scribbles_]

Hi everyone,

I have a column with a series of numbers, and I can easily get the median from this summary above it, but for the life of me I can't figure out a formula to get the same results in a separate table column.

Ideally I want this data in a table so I can use it for other calculations. I've been searching for hours but can't find anything. The closest I can get to this is using the rollup field to roll up all the numbers and then use the AVERAGE formula, but I need to get the median average. Would anybody know a formula I can use to get the median? I've tried other formulas from other places on the forums, but they don't seem to work for me.

[ScottWorld]

Unfortunately, Airtable doesn’t offer a MEDIAN function for formulas nor rollup fields. Like you said, Airtable only offers a MEDIAN function as part of the summary bar. But unfortunately, Airtable doesn’t let us use those summary bar values in any way in the app — except that you can build an interface and add a number element to your interface which contains the summary value.

But outside of interfaces and the summary bar, there is no built-in way to get a median value in Airtable. W. Vann Hall has a hack here, but it won’t work under all circumstances: 

I don’t know JavaScript, but assuming that JavaScript offers a median function and that you know how to write JavaScript code, you could write your own custom JavaScripts to return the median for you.

Otherwise, for a no-code way of handling this, your other option is to use an automation tool like Make to loop through your Airtable records and send the records to Excel or Google Sheets, both of which have MEDIAN functions. So your median would be in a spreadsheet instead of in Airtable. There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. (Alternatively, if you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with this, please feel free to contact me through my website: Airtable consulting — ScottWorld).

[Karlstens]

Morning @Scribbles_ 

Here is a Scripting Extension script that I put together with my Sunday morning coffee.

const calculateMedian = (data) => {
    // Extract the 'name' values and convert them to numbers
    const numbers = data.map(item => parseFloat(item.name));

    // Sort the numbers
    numbers.sort((a, b) => a - b);

    // Calculate the median
    let median;
    const len = numbers.length;
    if (len % 2 === 0) { // even length
        const mid = len / 2;
        median = (numbers[mid - 1] + numbers[mid]) / 2;
    } else { // odd length
        const mid = Math.floor(len / 2);
        median = numbers[mid];
    }

    return median;
}

let table = base.getTable("Calculation");
let record = await input.recordAsync('Pick a record', table);
if (record) {
   
    await table.updateRecordAsync( record.id, { 
       'Return': calculateMedian(record.getCellValue("Data"))
    });
    output.text(`Medium for record is ${calculateMedian(record.getCellValue("Data"))}`)
    output.text(`You picked ${JSON.stringify(record.getCellValue("Data"),null, 2)}`);
}

Unfortunately I have to get on with weekend tasks so can't build this further, but the idea I had was you have a calculation table that allows you to group numbers and calculate a return from those numbers that returns as the groups name.

There's potential here to add more types of calculations, but it also lines up a few challenges, such as, what happens when the same data is committed to different calculations? Perhaps that's not an issue here.

Note that the Single Select field hasn't been implemented in the script - but the idea is that it could be, and it would trigger the script within an Automation upon change.

Hmm, maybe we at least need a group ID for the Calculation Table;

Allows us to then group on the Data table;

Hope this gets you thinking about alternative ways to solve the problem.

I've requested Airtable to add more math functions to their formula/expressions, such as Median(). It's a right pain not having it.

[Scribbles_]

Thank you both for the suggestions and formula on how to derive this median average. I'll try out your script when I can Karlstens!

A workaround I managed to come up with was to use a Pivot table extension and group by task. Pivot tables allow you to summarize by median average, along with most of the other types of formulas only accessible from the Airtable summary bar.

This allows me to copy paste the information I need into the other table where I need to parse the points. Not the most automatic solution, but I'll take it.