Skip to main content

Standard deviation in calculations?

  • January 21, 2018
  • 5 replies
  • 170 views
P

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

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • January 21, 2018

    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.

      P
      • Paul_Teplitz
      • Author
      • Participating Frequently
      • January 22, 2018

      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.


      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!

      S

      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.


      Really happy if you can share your base :slightly_smiling_face:

      W
      Forum|alt.badge.img+5
      • W_Vann_Hall
      • Inspiring
      • June 2, 2018

      Really happy if you can share your base :slightly_smiling_face:


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

      J
      Forum|alt.badge.img+18

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

        Terms & ConditionsAccessibility statement