Forum Discussion

Deleted's avatar
Deleted
Not applicable
Apr 26, 2017

Standard Deviation

I am trying to build this data set. My goal is to do all this work now, so that we can copy and paste information and it will (mostly) be completed. 

My recent snag: By copying and pasting the results from Qualtrics, the answers are names (i.e. Strongly agree, agree) and not numbers. However, in our excel sheet we equate them to numbers (Strongly agree=5, agree=4, etc.). I have used COUNTIFs to figure out means and descriptive statsitcs, but I am stuck on Standard Deviation. Obviously, you can't figure out the std. devation of a name, but I was hoping of some how figuring it out. 

 

My current solution is to create ANOTHER section where it translates the names (Strongly Agree) into numbers off to the side and then using that one. This still keeps the goal of keeping the copy and paste mechanic, with it being automatically updated. I have to keep that copy and paste from qualtrics (if possible) to make it easier for future generations.

Issue: Can't deteremine standard deviation based off of names. I want to keep the mechanic of just copy and pasting the qualtrics data. 

Thanks!

Sam

Formulas & Functions
tips and tricks

1 Reply

  • Jon Wittwer's avatar
    Jon Wittwer
    Tin Contributor
    May 01, 2017

    Samuel, there are a few ways you can accomplish this. One method is to calculate the mean and standard deviation from your table of frequencies and values.

     

    The mean could be calculated as =SUMPRODUCT(frequency,value)/SUM(frequency)

    where in your test worksheet, frequency would be the range J108:J112 and value would be K108:K112 and cell K123 would be

    =SUMPRODUCT($J$108:$J$112,K108:K112)/SUM(K108:K112)

     

    The standard deviation would be

    =SQRT(SUMPRODUCT((value-mean)^2,frequency)/(N-1))

    using (N-1) for sample standard deviation or just (N) for the population standard deviation where N=SUM(frequency) and mean is the value you calculated in cell K123.

     

    In your file, cell K124 would be

    =SQRT(SUMPRODUCT(($J$108:$J$112-K123)^2,K108:K112)/(SUM(K108:K112)))

     

    To understand these formulas, you'll need to look up the mathematical formulas for mean and standard deviation and also look up how SUMPRODUCT works.