standard deviation

[afdmello]

my data is as follows


row 1 1 2 3 4 5
row2 2 3 7 10 8

the explanation is as follows: Row 1 is the degree of rating for a movie
with 1 being the lowest and 5 highest.Row is the number of persons
responding to the questions in total there are 30 respondents.

How do I find the average and the standard deviation for such data.

Average I found out by using the sumproduct of row 1 and 2 and dividing by
30. How do I find the standard deviation???

AFd

 

[Tyro]

Insert function/search for functions - enter "standard deviation". returns
STDEV Excel help takes you a long way.
Tyro

 

[Mike Middleton]

AFd -

A Google search for "excel standard deviation grouped data" without the
quotes yields numerous results. The first hit is
http://research.stowers-institute.o.../Applications/Excel/ExcelGroupedMeanStDev.pdf

Your measurements are ordinal (measurements based on rank, not ratio or
interval scale). Strictly speaking, you should not treat the integers 1
through 5 as having the same meaning as ratio or interval data. So, you
should not use mean and standard deviation. Instead, use median and
fractiles. It is unlikely that a 4 is twice as much as a 2, and it is
unlikely that the difference between 1 and 2 is the same as the difference
between 3 and 4, and it is unlikely that interpersonal ratings have any
consistent meaning. But, it is certainly the case that many results for your
kind of data are summarized using mean and standard deviation.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Stan Brown]

The first question is not "how" but "should I"? Mean and standard
deviation are appropriate when you have data in a linear scale, where
for instance 3 is 3 times as much as 1. Do people who rated a movie 5
like it five times as much as people who rated it a 1? Do they like
it 1.666666 times as much as people who rated it a 3? Those seem
rather unlikely.

A better approach IMHO (though more work) is to show a small bar
graph for each movie.

You have to go back to the formula, but again: I really question
whether mean or standard deviation is appropriate here. If you are
bent on computing those numbers, use a Google search like this one:
"standard deviation" "frequency distribution" excel

Insert function/search for functions - enter "standard deviation". returns
STDEV Excel help takes you a long way.

Or not.

Excel has functions to find standard deviation of a simple list of
numbers, but the OP has a frequency distribution. If there's a
function that will find the standard deviation of a frequency
distribution, I don't know what it is and I couldn't find it in Excel
help.

 

[joeu2004]

Insert function/search for functions - enter "standard deviation".
returns STDEV Excel help takes you a long way.

I don't believe AVERAGE and STDEV work with grouped data.

Moreover, STDEV computes a sample standard deviation. It is unclear
whether the OP really wants to compute a population standard deviation
(STDEVP).

my data is as follows
row 1 1 2 3 4 5
row2 2 3 7 10 8
the explanation is as follows: Row 1 is the degree of rating for a movie
with 1 being the lowest and 5 highest.Row is the number of persons
responding to the questions in total there are 30 respondents.
How do I find the average and the standard deviation for such data.

The average can be computed with (in A4):

=sumproduct(A1:E1,A2:E2) / sum(A2:E2)

The population standard deviation can be computed with either of the
following equivalent formulas:

=sqrt(sumproduct(A2:E2,(A1:E1-A4)^2) / sum(A2:E2))

=sqrt(sumproduct(A2:E2, A1:E1^2)/sum(A2:E2) - A4^2)

For a sample standard deviation, change SUM(A2:E2) to (SUM(A2:E2)-1).

Others have expressed concern about using mean and standard deviation
in this context.

 

[afdmello]

Thanks Joeu you response seems beneficial. I am only a secretary trying to
use the formula for my boss. Can you enlighten me on the meaning of "
sample" & "population"

 

[joeu2004]

I am only a secretary trying to use the formula for my boss.

Be sure to share the concern expressed by others about computing
statistics on so-called "qualitative" data. I have mixed feelings in
this case.

In light of those concerns, my explanation below should be taken with
a grain of salt. It is intended to be general in nature.

Can you enlighten me on the meaning of "sample" & "population"

If you want to compute statistics that describe just those 30
respondents, they are "population" -- that is, the complete set of
data that you want to talk about. Given the dubious use of statistics
on movie rankings in the first place, I would say this is your own
option.

On the other hand, if you want to compute statistics about the 30
responses for the purpose of drawing inferences about a larger
population, the 30 responses represent a "sample" of the larger
population. That calls for different formulas -- usually minor
differences like subtracting one here and there. For example, use
Help to see the difference between STDEV and STDEVP.

The concern that others have expressed is: usually we can compute
statistics like standard deviation only on quantitative variables --
variables that represent a count or measurement. Movie rankings are
usually considered qualitative data -- like color of the hair. For
qualitative data, we usually use percentile statistics and visual
methods of comparison.

(Example: If 10 people have blond hair and 5 people have brown hair,
you cannot say that the average hair color is "blond and a third",
even if you assign 1 to blond and 2 to brown. Many people the same
thing about movie rankings.)

 

[afdmello]

thanks Joeu,
I will use the data only for the 30 and it is a finite set and not for
inference just a way to indicate the ratings.I will try and incorporate the
formuls given by you

 

[afdmello]

I have inserted your formula and obtained the average to be 4.5 but the
standard deviation value is 0.8 which means the valued lie +or - 0.8 units
above 4.5 which is not logical as the average cannot be greater than 5 for
eg

row 1 1 2 3 4 5
row 2 0 1 2 8 19

average comes as 4.5 and std deviation using population formula comes as 0.8
or have I done any mistake

thanks

Afd

 

[joeu2004]

I have inserted your formula and obtained the average to be 4.5 but the
standard deviation value is 0.8 which means the valued lie +or - 0.8 units
above 4.5 which is not logical as the average cannot be greater than 5 for
eg

row 1 1 2 3 4 5
row 2 0 1 2 8 19

average comes as 4.5 and std deviation using population formula comes as 0.8
or have I done any mistake

First, I never said it would be useful or "logical". I was simply
giving you what you asked for: the Excel formula to compute the
mathematical definition of std dev for grouped data.

Second, your conclusion ("values lie +/- 0.8 units around 4.5") is not
logical insofar as that is not what the std dev tells you. The std
dev is merely a measure of dispersion of data around the mean. It is
not the only measure of dispersion to use in a statistical analysis,
as Mike and Stan tried to explain. You may have simply learned that
they were right in the first place.

Moreover, the std dev does not (always) tell you anything about the
range of the data. You might be thinking of the interpretation of the
std dev in a "normal distribution" of data, which means that the
(population) data meets certain criteria. (Yours do not.) But even
in that context, we would expect only about 68% of the (population)
data, not all of the (population) data, to lie within +/- 1 sd (0.76
in your case) of the mean.

Finally, even if the std dev could be interpreted as a range for the
data, the range would be clipped (bracketed) by any real-world
constraints, e.g. 5 in your case. So hypothetically (and incorrectly
in your case), we would conclude that the range of data is -0.76 to
+0.50 around the mean. (But I reiterate: that conclusion is not
valid.)

At this point, I think it is fair to say that we have gone beyond the
Excel question and wandered into tutorial statistics. I am not
prepared to explain the latter in this forum.

 

[Bernd P]

Hello,

We discussed this recently in the German Excel newgroup.

I suggest to use my UDF swv:
http://www.sulprobil.com/html/statistics_for_weighted_values.html

Regards,
Bernd