Standard Deviation STDEV - Quick Reference

[Jeremy_Oglesby]

For those of you needing to perform a Standard Deviation calc (STDEV() in Excel), here’s a template you can use for performing this calculation with values across a single row (record):

SQRT(  
  SUM(
    POWER({Value 1 Field} - {Avg of Values Field}, 2),
    POWER({Value 2 Field} - {Avg of Values Field}, 2),
    POWER({Value 3 Field} - {Avg of Values Field}, 2),
    ...
  ) / (n - 1)
)

Where n = the number of fields SUM’d, ie, the number of POWER() statements.

You do have to manually enter each field that holds a value which you want factored in to the StDev calculation, and you have to have a field that calculates the averages of those values, so this will not work well for a dynamic data set (new values are constantly being added to the list of values needing to be included in the calculation).

I use it quite frequently in calibration specs, however, so I figured I’d post it here as a quick and easy reference for other people who were used to using the STDEV() function in Excel, but are not sure how to translate that into Airtable for simple data sets.

You just need an instance of this line for each value you need to include in the calculation:

POWER({Value 3 Field} - {Avg of Values Field}, 2)

• Ensure that each POWER() statement has a comma after it EXCEPT the last one in the list.
• Ensure that you replace n with the number of POWER() statements you included.

[Tom_Quigley]

Hey Jeremy - thanks for this. Stats is not my strong suit, so I hope I don’t fumble to blindly here.

I have a series of scores from judges that I’d like to Z-score. Each project is scored by 3 judges, and each judge has scored 10 projects across 7 criteria on a 1-5 scale. Each project’s criteria & total score from each judge is on one row in the Scoring table.

So according to this, I should add a Function property on the Scoring sheet and add the Power equation. But just to be clear:
{Value 3 Field} in this case would be changed for {indiv. judge’s score total for this project} and
{Avg of Values Field} would be replaced by {average indiv. judge’s score totals, across all projects they scored}?

Hope I explained that correctly; please feel free to DM me with clarifying questions.

[Jeremy_Oglesby]

Hey @Tom_Quigley — so sorry I am just now seeing this! Since it has been some time now, have you found a workable solution? Or are you still trying to implement your Z-Score formula?

(for future reference, using @ tags can be a good way to get someone’s attention and ensure your response doesn’t get buried under all the rest)

[Michael_Andrew]

What I find interesting is AT does the calculation (it’s in the summary bar for the field) but doesn’t offer as roll up.

Standard Deviation as Roll up Field Product Suggestions

It would be great if Airtable had a STDEV roll up field. I would think it would be easy, since the calculation is available (you can set the field summary at the bottom to stdev). To create a manual formula is difficult with variable data otherwise. Please add these feature (as well as other statistics that are already available and calculated in the summary at the bottom of the field).

I would think since they are already calculating it would be easy enough to offer.

Maybe people aren’t doing stats in AT much. Things like percent rank and other common spreadsheet formula would be valuable additions as roll ups.

[Tom_Quigley]

Hey @Jeremy_Oglesby and @Michael_Andrew - thanks for the tips.

I just received the first tranche of scores from judges, so I am going to start working on this in Airtable this week. I paused work on it a few weeks back as it wasn’t the most important thing to get done.

Michael, that’s really interesting about the StDev in the rollup field. Also interesting, it’s not supported in the actual Rollup property (though that seems to rely on the Function text box, so perhaps not surprising).

I had given up on trying to calculate StDev within Airtable, was going to just do it in Excel and then paste it into cells (since the judges scores won’t be changing after I receive them, the StDev doesn’t need to by dynamically calculated) - but with this rollup method I can cut out the middle-app and just paste direct from the rollup.

Jeremy - I’ll be testing that this week and report back if the Z-scores come out all wonky!

Thanks both for your help.

[Michael_Andrew]

For some stats, I use an integration with google sheets where I then calculate and then feedback to AT using Zapier. This middle man technique in not very elegant but it works. It would be much better if AT just had these roll up functions (which they do have in the summary bar but don’t make available as rollups)

[Tom_Quigley]

Update: the rollup at the top of the field didn’t calculate to enough decimal places, so I have been pasting my scores out to Excel, calculating STDEV there, and then pasting that back in. That’s been enough for me to calculate z-score pretty easily. Thanks all!