how do I create a frequency distribution chart in excel to assess.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to look at a frequency distribution graph for data in excel to assess
whether the data is normally distributed before applying parametric
statistical tests.
Is there a simple way of producing such a graph
 
Tried that, it generates a histogram of frequency of individual data points,
not a frequency distribution that is useful for assessing normality. I want
to produce a histogram of the frequency distribution in the same fashion that
Minitab & SPSS do (together with statistical tests of normality that are not
available in Excel).
This should not be so complicated as normality should be being tested before
many of the parametric statistical tests that are provided by excel are used.
 
For visual evaluation, the histogram is far more useful than a
cumulative frequency graph.

A more discriminating qualitative approach would be to do a normal
probability plot. Mike Middleton has a downloadable paper on this at
http://www.usfca.edu/~middleton/data.htm

Excel is not a statistics program (though it can be used to do
statistics) and has no built-in normality tests.

The usual quantitative tests, such as Shapiro-Wilk would require that
you insert tabled values
http://www.itl.nist.gov/div898/handbook/prc/section2/prc213.htm
or use a commercial Add-In such as
http://www.analyse-it.com/shapiro-wilk-normality-test_y.htm

You could do a rough and ready chi-square test, by dividing the real
line into a n intervals, each having an expected value under normality
of at least 5 observations with your sample size. Then sum
(Observed-Expected)^2/Expected and compare to CHIINV(alpha,n-1) where
alpha is suitably small (say 0.05)

More generally, you could do a Kolmogorov-Smirnov test, but again you
would have to program it yourself.

If you are wanting to do heavy duty statistical analysis on a budget and
no longer have access to Minitab and SPSS, have a look at R. It is an
open source statistical package (using the S language) that is freely
available
http://www.r-project.org

Jerry
 
Back
Top