Skip to main content

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.

 

 

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 completely no-code way of handling this, your other option is to use an automation tool like Make’s automations & integrations, which has a MEDIAN function built into their app.

You could even use Make to loop through the records and send the records to Excel or Google Sheets, both of which have MEDIAN functions as well.

If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable. 

I also give live demonstrations of how to use Make in many of my Airtable podcast appearances. For example, in this video, I show how to work with Airtable arrays in Make.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

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.

 

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. 

 

I know this is a bit of an old thread, but as a new user, I found I could find the median using ArraySlice on a rollup field using this formula:

(ARRAYSLICE(values,ROUNDUP(Count/2,0),ROUNDUP(Count/2,0))+IF(MOD(Count,2)=0,ARRAYSLICE(values,ROUNDUP(Count/2,0)+1,ROUNDUP(Count/2,0)+1)))/IF(MOD(Count,2)=0,2,1)

 

It works for both even and odd counts. The key here is to sort the records by the field you’re summarizing in the rollup field setup.

 

Adding here in case someone needs this easy solution!

Reply


Terms & Conditions