Category Archives: Computational Tools

Computational tools that get us to the business of assessment

R Is Great

(Valentines day disclaimer) I love R. It was the first free statistics package that I used and it plugin framework makes it great for all sorts of uses.

Advertisements

2 Comments

by | February 14, 2013 · 10:42 am

Weighted Average Standard Deviation

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

=SUMPRODUCT(A1:A5,B1:B5)/(SUM(A1:A5)

The variance (C2) of the scores is

=SUMPRODUCT(A1:A5,(B1:B5-C1)^2)/(SUM(A1:A5)-1)

The standard deviation is the square root of the variance

=SQRT(C2)

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.

2 Comments

Filed under Computational Tools, Methods, Statistics