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

[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

 

[Jerry W. Lewis]

Install the Analysis ToolPak and use Tools|Data Analysis|Histogram

Jerry

 

[Tushar Mehta]

While Jerry answered your specific question, you might also want to
look at http://www.usfca.edu/~middleton/demand.pdf

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

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.

 

[Jerry W. Lewis]

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