Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Standard deviation in calculations?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
7159
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 20, 2018 09:41 PM

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.)

5 Replies 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2018 07:46 AM

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:

- Calculate the mean of the author’s reviews

`(R1 + R2 +R3 + .. + Rn)/n`

= m - Calculate the variance

`(R1-m)^2 + (R2-m)^2 + (R3-m)^2 .. (Rn-m)^2)/n`

= v - 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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2018 07:18 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 02, 2018 04:42 AM

Really happy if you can share your base :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 02, 2018 04:58 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 23, 2019 12:45 PM

Reply