# Find the median average from a rollup field

Topic Labels: Formulas
1062 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

3 Replies 3
18 - Pluto

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.

11 - Venus

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.

5 - Automation Enthusiast

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.