We recently found a way to calculate the standard deviation of weighted values on a Likert scale using Excel. The three formulas involve first finding the weighted average score followed by variance and standard deviation. This was important because Excel does not provide a standard formula to find standard deviation with weighted values. Assuming that A1: A5 has the raw values and B1:B5 has the respective scores, the formulas are as follows:
The average score (C1) is
The variance (C2) of the scores is
The standard deviation is the square root of the variance
This finding is important for the Office of Assessment because more data analysis can be done on Likert scale survey questions like those in the AACP surveys. It provides a greater statistical understanding of weighted values and more possibilities as we continue to expand our ideas for future research.
Details can be found on Excel Banter.