Standard deviation in calculations?

[Paul_Teplitz]

Is there a way to use standard deviation in calculations? I.e., is there a standard deviation formula, or as a roll-up aggregator?

Use case: I have multiple users reviewing applications and rating the apps within a rubric. Multiple people are reviewing each application. Using Zapier, I have the scores from the reviewers automatically loading into an “AppReads” table, with one row per app/reviewer instance. Because of the number of reviewers, I need to normalize the scores by reviewer - I need a t-score. So, I have a reviewer table that is linked to the AppReads table. Using rollups I can easily calculate the mean by reviewer. In order to calculate t-scores, though, I need the SD.
Could this be added as an rollup function? Or is there a work around I’m not seeing?

(I know that the Summary bar include standard deviation as an option, but I can’t figure out how to use that in calculations.)

[W_Vann_Hall]

You’re in luck. I just hammered this out yesterday. Exactly how you do it is going to depend on how your base is structured, but once you have the mean, you need to calculate the variance, which you would do by subtracting the mean from each score, squaring the difference, summing the squared differences, and then averaging the result. (That is, after adding the squared differences, divide the sum by the number of reviews.) That gives you the variance; to get the standard deviation, take the square root of the variance.

Edit: On rereading, that wasn’t anywhere near as coherent as I thought. Here’s a slightly different take on the topic:

1. Calculate the mean of the author’s reviews
   (R1 + R2 +R3 + .. + Rn)/n = m
2. Calculate the variance
   (R1-m)^2 + (R2-m)^2 + (R3-m)^2 .. (Rn-m)^2)/n = v
3. Get the standard deviation
   SQRT(v) = SD

I haven’t finished the base, yet, so I can’t post it; still, it’s a reasonably straight-forward formula, and the only interesting parts are implementation-specific to my base. This page and this one offer a simple yet comprehensive discussion of the concept and math.

[Paul_Teplitz]

Amazing. It’s been so long since I’ve taken stats I forgot it really isn’t that hard to calculate the standard deviation manually. It’s not quite as simple as a rollup formula, but it’s not that hard to implement. The most challenging thing for me is that I want to normalize by reviewer and by question, so I’ll need to calculate the standard deviation for each question (i.e., once for each of the 9 things reviewers are responding to). It’ll be a little annoying, but not hard.

Thanks so much for reminding me to not always rely on a pre-packaged formula!

[Stefan_in_t_Vel]

Really happy if you can share your base :slightly_smiling_face:

[W_Vann_Hall]

As I recall, the base in question was my Black Mirror base, which has since been published in Airtable Universe.

[Jeremy_Oglesby]

This post is old, but for the sake of future searchers, I’ve provided a condensed Standard Deviation formula template here:

Standard Deviation STDEV - Quick Reference Show and tell

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 e…