Standard deviation script?

[Tom_Quigley]

Currently in Airtable there’s no function provided for a standard deviation like Excel’s STDEV. One user on this community has helpfully written out the function that calculates it, but it’s rather complex.

I’m wondering if the script block couldn’t be used with math.std on rolled-up values in a single property to very quickly generate the standard deviation on a set of values?

I’m still verrry green at Javascript but this seems like it would work; if you agree I’d love some guidance on where to start on pulling the values from a roll-up column and using them in the math.std calculation.

Thank you!

[Tom_Quigley]

OK, I’m not sure this will work. I can’t find it on MDN JS docs, but I found it here which is what made me think I could in the first place.

Here’s my (VERY GREEN) attempt at making this happen from a single field with multiple number values. It doesn’t work. If you think this approach might work, I would hugely appreciate some JS guidance!

// pull the tables we will be using
let table = base.getTable('Judges');
let query = await table.selectRecordsAsync();

// define a function that does standard deviation to a series of numbers in a Field
function getStDev = () {
    let Scores = {table.getCellValue('All total scores')};
    let scoresStDev = math.std(Scores);
    return scoresStDev;
}

// run that function
console.log(getStDev());

// write that number to a Field
await table.updateRecordsAsync([
        {
                fields: {
                    'id': judge.id,
                    'Standard Deviation': scoresStDev,
                },
            },
        ]);

[JonathanBowen]

Hi @Tom_Quigley - a couple of pointers here:

• table.getCellValue isn’t a valid method, so this will be giving an error.
• You’re using an external library - MathJS - but, unfortunately, you can’t just call these methods as Airtable doesn’t know about them. Ideally you could import this library into your script, but that feature isn’t available right now, so you need to bring the code directly into your script. MathJS is a pretty big library, so you probably don’t want to use this. A quick Google pointed me to this (I’m assuming that the calculations are good, but I haven’t validated as such.)

So, with a table like this:

you want to calc the STD of the “Scores” field for each Judge, right?

My script is this:

let table = base.getTable('Judges');
let query = await table.selectRecordsAsync();

for (let judge of query.records) {
    let scoresArray = judge.getCellValue('Score');
    let scoresAsNumbers = scoresArray.map(element => {
        return Number(element)
    });
    console.log(scoresAsNumbers);
    console.log(average(scoresAsNumbers));
    console.log(standardDeviation(scoresAsNumbers));
}

// STD and AVG functions copied from:
// https://derickbailey.com/2014/09/21/calculating-standard-deviation-with-array-map-and-array-reduce-in-javascript/

function standardDeviation(values){
  var avg = average(values);
  
  var squareDiffs = values.map(function(value){
    var diff = value - avg;
    var sqrDiff = diff * diff;
    return sqrDiff;
  });
  
  var avgSquareDiff = average(squareDiffs);

  var stdDev = Math.sqrt(avgSquareDiff);
  return stdDev;
}

function average(data){
  var sum = data.reduce(function(sum, value){
    return sum + value;
  }, 0);

  var avg = sum / data.length;
  return avg;
}

If your table is like mine, the array of values returned from the “Scores” field is an array of strings:

["8", "40", "54"]

So I’ve mapped that to an array of numbers which we can then do the STD on.