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.

Advertisements

2 Comments

Filed under Computational Tools, Methods, Statistics

2 responses to “Weighted Average Standard Deviation

  1. Love institutional memory…

    • Anthony Marziliano

      I’ve been thinking about this idea of ‘Institutional Memory’ over the past few days. This could really be a vehicle for us to remember things we do and the ways we do them. Pretty exciting to finally have a place to store thoughts and methods…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s