Remember Me?

#### Menu

#1
October 1st 05, 08:42 PM
 kthenning Posts: n/a
Weighted Average Standard Deviation

I'm doing a customer survey where people have responded:

Agree strongly 331
Agree somewhat 100
Neither 50
Disagree somewhat 10
Disagree strongly 5

I want to assign a 1 to 5 score to each response (1=agree strongly) and get
the weighted average standard deviation using just the frequencys above. Is
this possible in Excel? If so, what would the equation be? I saw another
post about a wmean, wsd...but the equation returns a !NAME error.
Please help...Thank you

#2
October 1st 05, 09:26 PM
 [email protected] Posts: n/a

kthenning wrote:
I'm doing a customer survey where people have responded:
Agree strongly 331
Agree somewhat 100
Neither 50
Disagree somewhat 10
Disagree strongly 5
I want to assign a 1 to 5 score to each response (1=agree strongly)
and get the weighted average standard deviation [...].
Is this possible in Excel?

There might be an easier way, but the following works,
and it straight-forwardly follows the math definitions.

Assume that A1:A5 has the values above, and B1:B5 has
the respective scores. Then the average score (C1) is:

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

and the variance (C2) of the scores is:

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

The standard deviation is simply the square root of
the variance, namely:

=SQRT(C2)

Note: The formulas assume that you want to treat the
responses as samples. For the population average and
variance, remove "-1" in the denominator.

#3
October 1st 05, 09:44 PM
 kthenning Posts: n/a

Thank you!!

" wrote:

kthenning wrote:
I'm doing a customer survey where people have responded:
Agree strongly 331
Agree somewhat 100
Neither 50
Disagree somewhat 10
Disagree strongly 5
I want to assign a 1 to 5 score to each response (1=agree strongly)
and get the weighted average standard deviation [...].
Is this possible in Excel?

There might be an easier way, but the following works,
and it straight-forwardly follows the math definitions.

Assume that A1:A5 has the values above, and B1:B5 has
the respective scores. Then the average score (C1) is:

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

and the variance (C2) of the scores is:

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

The standard deviation is simply the square root of
the variance, namely:

=SQRT(C2)

Note: The formulas assume that you want to treat the
responses as samples. For the population average and
variance, remove "-1" in the denominator.

#4
October 2nd 05, 01:53 PM
 Jerry W. Lewis Posts: n/a

#5
October 2nd 05, 05:03 PM
 [email protected] Posts: n/a

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post bob green Excel Worksheet Functions 1 August 1st 05 10:33 PM bob green Excel Worksheet Functions 1 August 1st 05 06:31 AM BillC Excel Worksheet Functions 3 May 3rd 05 04:13 PM Li Excel Worksheet Functions 1 April 12th 05 09:44 PM Jens Eichelbaum Excel Worksheet Functions 2 November 23rd 04 02:10 AM

All times are GMT +1. The time now is 08:11 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2004-2017 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017