A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Weighted Average Standard Deviation



 
 
Thread Tools Display Modes
  #1  
Old October 1st 05, 08:42 PM
kthenning
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old October 1st 05, 09:26 PM
[email protected]
external usenet poster
 
Posts: n/a
Default

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  
Old October 1st 05, 09:44 PM
kthenning
external usenet poster
 
Posts: n/a
Default

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.
>
>

  #5  
Old October 2nd 05, 05:03 PM
[email protected]
external usenet poster
 
Posts: n/a
Default

Jerry W. Lewis wrote:
> wrote:
> > 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)

>
> I think you meant
> =SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)


Yes, you are right. Overzealous editing. Only the
variance formula changes for sample v. population
statistics.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
calculating a weighted average using formula bob green Excel Worksheet Functions 1 August 1st 05 10:33 PM
calculating a weighted average uisng formula bob green Excel Worksheet Functions 1 August 1st 05 06:31 AM
Show weighted average value after filter. BillC Excel Worksheet Functions 3 May 3rd 05 04:13 PM
How to calculate 2 standard deviation? Li Excel Worksheet Functions 1 April 12th 05 09:44 PM
Histrogramms: Calculating average and standard deviation Jens Eichelbaum Excel Worksheet Functions 2 November 23rd 04 03:10 AM


All times are GMT +1. The time now is 10:29 AM.


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